|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
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!
|
|
|
|
Joined: Dec 2000
Posts: 1,471
Addict
|
Addict
Joined: Dec 2000
Posts: 1,471 |
Can you post the table structure and mark the foreign keys? It's hard to give a probably easier solution without knowing the structure.
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
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?
|
|
|
|
Joined: Dec 2000
Posts: 1,471
Addict
|
Addict
Joined: Dec 2000
Posts: 1,471 |
Trail_Type and Countries are missing.
Can you explain in words what your result should be?
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
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 <br />state_id state_name<br />67 New York<br />68 Pennisilvania<br />etc.... <br /> 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?
|
|
|
|
Joined: Aug 2002
Posts: 1,191
Kahuna
|
Kahuna
Joined: Aug 2002
Posts: 1,191 |
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.
Nikos
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
Ah - "AS" That makes sense. Thank you - I will give that a try. Many thanks to both of you.
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
Woo - Hoo! Thanks Nikos! That did the trick. Thanks also Astaran for helping.
|
|
|
|
Joined: Dec 2000
Posts: 1,471
Addict
|
Addict
Joined: Dec 2000
Posts: 1,471 |
Thx Nikos, i was too tired to think about it and decided to go to bed instead.
|
|
|
|
Joined: Aug 2002
Posts: 1,191
Kahuna
|
Kahuna
Joined: Aug 2002
Posts: 1,191 |
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
Nikos
|
|
|
|
Joined: Nov 2001
Posts: 10,369
I type Like navaho
|
I type Like navaho
Joined: Nov 2001
Posts: 10,369 |
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.
|
|
|
Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.
Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
|
|
Posts: 1,157
Joined: July 2001
|
|
Forums63
Topics37,573
Posts293,925
Members13,849
|
Most Online5,166 Sep 15th, 2019
|
|
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
|
|
|
|