Page 1 of 1

Joining/querying unrelated tables - PLEASE HELP!

Posted: Sat Feb 27, 2010 6:06 pm
by duncanjd
Please please please can someone help with this. I'm fairly new to php and mysql and am having some trouble returieving the data i want in the way that i want to.

i have set up three table. They are not exactly like this but this best explains the principles of my aim. These are their table structures.

1. States
-----
id
state

2. towns
-----
id
state_id
town

3. residents
--------
id
town_id
first_name
surname
etc etc........

I want have created a search form but am struggling with the sql. I want to perform a query whereby i can retrieve all the residents of one particular state, at the moment i only know how to do for the town.

I know i could do this by adding a state_id column to the residents table but i figured there must be a better way to do this i just cant figure it out. Can someone please help. Thanks.

Duncan

Re: Joining/querying unrelated tables - PLEASE HELP!

Posted: Sat Feb 27, 2010 6:33 pm
by AbraCadaver
Something like this should work if you know the state_id:

Code: Select all

SELECT * FROM Residents
   LEFT JOIN Towns ON Residents.town_id = Towns.id
   WHERE Towns.state_id = 1
If you just have the text that would be in States.state, then this might work:

Code: Select all

SELECT * FROM Residents
   LEFT JOIN Towns ON Residents.town_id = Towns.id
   LEFT JOIN States ON Towns.state_id = States.id
   WHERE States.state = 'TX'

Re: Joining/querying unrelated tables - PLEASE HELP!

Posted: Sun Feb 28, 2010 8:16 am
by duncanjd
Thank you so much, it worked like a dream, life saver. I had been been racking my tiny brain for hours. I had even found a text book and read about joins and knew that that was what i had to do. Until i saw it in the context of my own work though i couldnt get my head round it.

Thanks again for the swift reply, much appreciated. :D