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_headline
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) | | | | |
+---------------------+-------------+------+-----+---------+----------------+
--
Keith Bussey
Director of Operations - IwantU, Inc.
ICQ: 38373031
Tel: +506-280-2284 ext. 108
Fax: +506-253-2143
URL: http://www.IwantU.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
