Varius

Hi,

I am trying to come up with the most optimal way to write a query to select out the following fields:

test_username.username_name
test_subprofile_details.subprofile_details_headlin e
test_profile.profile_birthday
test_profile_gender.profile_gender_name
test_subprofile.subprofile_picture
test_country_region.country_region_name
test_country.country_name
test_profile_orientation.profile_orientation_name (there could be multiple rows)

With the following conditions:

profile_orientation_id IN (1,2)
profile_gender_id = 1
country_id = 1
site_id = 3

ORDER BY subprofile_last_update DESC
LIMIT 30


I am using MySQL 4.1 so subqueries are a valid option. I know I could do many queries to get my result, but I'm trying to see how I could do this in 1 query if possible, or the least amount of queries possible otherwise. I assume complex joins and/or subqueries need to be used, but I'm not very experienced with them.

Any help pointing me in the right direction, or giving me a solution (so then I could see how it was done for use in my future queries) would be greatly appreciated. Thanks in advance!

Tables Involved:

mysql> desc test_username;
+---------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------------+------+-----+---------+-------+
| uid | mediumint(8) unsigned | | PRI | 0 | |
| username_name | varchar(20) | | UNI | | |
+---------------+-----------------------+------+-----+---------+-------+

mysql> desc test_subprofile_details;
+----------------------------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------------+-----------------------+------+-----+---------+-------+
| uid | mediumint(8) unsigned | | MUL | 0 | |
| site_id | tinyint(1) | | MUL | 0 | |
| subprofile_details_headline | varchar(255) | | | | |
| subprofile_details_description | text | | | | |
| subprofile_details_description_seeking | text | | | | |
+----------------------------------------+-----------------------+------+-----+---------+-------+

mysql> desc test_subprofile;
+----------------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+-----------------------+------+-----+---------+----------------+
| subprofile_id | int(10) unsigned | | PRI | NULL | auto_increment |
| uid | mediumint(8) unsigned | | MUL | 0 | |
| site_id | tinyint(1) | | MUL | 0 | |
| subprofile_status_id | tinyint(2) | | MUL | 0 | |
| subprofile_last_update | int(10) unsigned | | MUL | 0 | |
| subprofile_picture | tinyint(1) | | MUL | 0 | |
| subprofile_voice | tinyint(1) | | MUL | 0 | |
| subprofile_video | tinyint(1) | | MUL | 0 | |
| subprofile_picture_gallery | tinyint(1) | | MUL | 0 | |
| subprofile_visits | int(10) unsigned | | | 0 | |
| subprofile_votes | mediumint(8) unsigned | | | 0 | |
| subprofile_rating | tinyint(2) | | | 0 | |
+----------------------------+-----------------------+------+-----+---------+----------------+

mysql> desc test_profile;
+---------------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+-----------------------+------+-----+---------+-------+
| uid | mediumint(8) unsigned | | PRI | 0 | |
| profile_birthday | int(10) unsigned | | MUL | 0 | |
| profile_gender_id | tinyint(1) | | MUL | 0 | |
| profile_marital_status_id | tinyint(1) | | MUL | 0 | |
| profile_body_type_id | tinyint(1) | | MUL | 0 | |
| profile_height | tinyint(3) | | MUL | 0 | |
| profile_zip | varchar(10) | | MUL | | |
| country_id | tinyint(3) unsigned | | MUL | 0 | |
| country_region_id | mediumint(8) unsigned | | MUL | 0 | |
| profile_city | varchar(80) | | | 0 | |
| profile_heritage_id | tinyint(1) | | MUL | 0 | |
| profile_faith_id | tinyint(1) | | MUL | 0 | |
| profile_smoke_id | tinyint(1) | | MUL | 0 | |
| profile_drink_id | tinyint(1) | | MUL | 0 | |
| profile_pet_id | tinyint(1) | | MUL | 0 | |
| profile_children_id | tinyint(1) | | MUL | 0 | |
| profile_allow_rating | tinyint(1) | | | 0 | |
+---------------------------+-----------------------+------+-----+---------+-------+

mysql> desc test_country;
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| country_id | tinyint(3) unsigned | | PRI | NULL | auto_increment |
| country_code | char(2) | | UNI | | |
| country_name | varchar(50) | | UNI | | |
+--------------+---------------------+------+-----+---------+----------------+

mysql> desc test_country_region;
+---------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-----------------------+------+-----+---------+----------------+
| country_region_id | mediumint(8) unsigned | | PRI | NULL | auto_increment |
| country_region_code | char(2) | | MUL | | |
| country_region_name | varchar(100) | | | | |
+---------------------+-----------------------+------+-----+---------+----------------+

mysql> desc test_country_link;
+-------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------------------+------+-----+---------+-------+
| country_id | tinyint(3) unsigned | | MUL | 0 | |
| country_region_id | mediumint(8) unsigned | | MUL | 0 | |
+-------------------+-----------------------+------+-----+---------+-------+

mysql> desc test_profile_orientation;
+--------------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-------------+------+-----+---------+----------------+
| profile_orientation_id | tinyint(2) | | PRI | NULL | auto_increment |
| profile_orientation_name | varchar(15) | | | | |
+--------------------------+-------------+------+-----+---------+----------------+

mysql> desc test_profile_orientation_link;
+------------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------+------+-----+---------+-------+
| uid | mediumint(8) unsigned | | MUL | 0 | |
| profile_orientation_id | tinyint(2) | | MUL | 0 | |
+------------------------+-----------------------+------+-----+---------+-------+

mysql> desc test_profile_gender;
+---------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+----------------+
| profile_gender_id | tinyint(1) | | PRI | NULL | auto_increment |
| profile_gender_name | varchar(15) | | | | |
+---------------------+-------------+------+-----+---------+----------------+

__________________
Skype variuscr - Email varius AT gmail