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
Joining/querying unrelated tables - PLEASE HELP!
Moderator: General Moderators
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Joining/querying unrelated tables - PLEASE HELP!
Something like this should work if you know the state_id:
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
WHERE Towns.state_id = 1Code: 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'mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: Joining/querying unrelated tables - PLEASE HELP!
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.
Thanks again for the swift reply, much appreciated.