UBB.Developers Forums Community Development Programming MySQL and Server Configuration Help with a complex JOIN

OK - doing this complex query (for me) which requires joining 4 tables. (I think - maybe there's a better way).

Here's my query (I edited out some so that it didn't scroll the screen)
SELECT t1.trail_name,t2.trail_type_desc,...,t1.state_id_origin,t1.state_id_destination,t4.country_name
FROM Trails AS t1,
Trail_Type AS t2,
States AS t3,
Countries as t4
WHERE trail_id = '$Trail_q'
AND t1.trail_type_id = t2.trail_type_id
AND t1.country_id = t4.country_id


trail_type_id, country_id, state_id_origin and state_id_destination are all numerical values - with the name defined in their own tables.

I had no trouble matching up the trail_type or the _country_name

But this is the part that confuses me:

state_id_origin and state_id_destination could be different values (the trail could start in one state and end in another).

If I replaced t1.state_id_origin and t1.state_id_destination each with t3.state_name and joined with
AND t1.state_id_origin = t3.state_id
AND t1.state_id_destination = t3.state_id

How does or can it keep them straight.

Right now the only way I can do this is with additional queries to lookup the state name based on the ids in state_id_origin and state_id_destination

Is there and easier way?

(I'm sure this didn't make sens - but it did to me)
My head hurts - now I know why Rick says he hates joins.

Thanks!

Can you post the table structure and mark the foreign keys?
It's hard to give a probably easier solution without knowing the structure.

Here's my Trails table:

CREATE TABLE Trails (
trail_id int(9) unsigned NOT NULL auto_increment,
trail_name varchar(255) NOT NULL default '',
trail_length_miles varchar(50) default NULL,
trail_highlights text,
trail_ranking char(2) default NULL,
trail_comments text,
trail_image varchar(50) default NULL,
trail_cities_encompassed text,
trail_directions text,
trail_URL varchar(100) default NULL,
approved tinyint(1) NOT NULL default '0',
trail_type_id int(9) unsigned default NULL,
city_origin varchar(50) default NULL,
state_id_origin int(9) unsigned default NULL,
city_destination varchar(50) default NULL,
state_id_destination int(9) unsigned default NULL,
country_id int(9) unsigned default NULL,
PRIMARY KEY (trail_id)
) TYPE=MyISAM;


And the States table:

CREATE TABLE States (
state_id int(9) unsigned NOT NULL default '0',
state_name varchar(50) default NULL,
state_abbr char(3) default NULL
) TYPE=MyISAM;

Is that what you need?

Trail_Type and Countries are missing.

Can you explain in words what your result should be?

OK -

t1.state_id_origin might be "67" and t1.state_id_destination might be "68".

I want to grab the t3.state_name for the origin (which might be "new york") where the state_id = 67 in the States table, and get the t3.state_name for the destination (which might be "pennisilvania") where the state_id = 68.

I guess it was setup this way so that the states and countries could easily be added - each in their own table.

Does that make sense?

So Data in the States table might look like this


Same theory for the countries table.


CREATE TABLE Trail_Type (
trail_type_id int(9) unsigned NOT NULL default '0',
trail_type_desc varchar(50) NOT NULL default ''
) TYPE=MyISAM;



CREATE TABLE Countries (
country_id int(9) unsigned NOT NULL default '0',
country_name varchar(50) NOT NULL default ''
) TYPE=MyISAM;


Thanks for the help?

Josh you are not getting the correct results because you are joining the same table twice.

What you should do is:

SELECT t1.trail_name,t2.trail_type_desc,...,t3.state_name AS Origin_State,t4.state_name AS Destination_State,t5.country_name
FROM Trails AS t1,
Trail_Type AS t2,
States AS t3,
States AS t4,
Countries as t5
WHERE trail_id = '$Trail_q'
AND t1.trail_type_id = t2.trail_type_id
AND t1.country_id = t5.country_id
AND t1.state_id_origin = t3.state_id
AND t1.state_id_destination = t4.state_id

That in effect will give you the Origin_State plus the Destination_State. I have used a double table join in the CMS 1.1 modification. If you see there the query joins the country table twice to determine the country of origin and the country of location.

I hope this helps.

Ah - "AS"

That makes sense.

Thank you - I will give that a try.

Many thanks to both of you.

Woo - Hoo!

Thanks Nikos! That did the trick.

Thanks also Astaran for helping.

Thx Nikos, i was too tired to think about it and decided to go to bed instead.

No problem Josh. And yes Astaran when it is time to go to bed the brain circuits seem to auto-shut It happens to me all the time and alarmingly during the day as well...

I am having brain fades... lol

I was proud of myself for getting as far as I did. I actually didn't need the "AS" in the select line, but did need to list the States table twice in the join as t3 and t4.

Now I know a new trick.