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

xenophobic

$time X hours = $$$
hire someone, do something for the economy?!

mikesouth

I am not going to do this for you for free but I can tell you this much. The performance will depend on your indexing moreso than the query itself although both are important.

__________________
Mike South

It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.

V_RocKs

The same way you wrote it out in laymans...

Varius

I have no problem hiring someone to do it if it was something important....

However since I'm asking because I'd like to learn it myself, I'd rather see the solution, that way I can see what was done exactly.

I'm not having any performance issues or problems at the moment, I'd just like to know how best this can be done.

Those who can give me a professional way to write the query using joins or subselects, send me your price anyways though [email protected] or ICQ 38373031

__________________
Skype variuscr - Email varius AT gmail

KRL

Keith,

I copied and sent to one of my coders who has a buddy who might be able to figure this for you.

I'll let you know asap.

K

__________________
If you would like to develop your domains, you can lease inexpensive foreign labor
from the leaders in the field at iWebmasters.com TO LOWER YOUR COSTS AND INCREASE YOUR PRODUCTION!
*** *** *** *** *** *** *** *** *** *** *** ***
Domains Adult News KRL's Newsletter Biz Tips Just Listed Domains

Varius

Cool....I have it done one way which works fine, but for my own curiosity I'd like to know the "real" way to do it (ie. the way pros would do it)

thx

__________________
Skype variuscr - Email varius AT gmail