Joining/querying unrelated tables - PLEASE HELP!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
duncanjd
Forum Newbie
Posts: 2
Joined: Sat Feb 27, 2010 5:56 pm

Joining/querying unrelated tables - PLEASE HELP!

Post 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
User avatar
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!

Post 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'
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.
duncanjd
Forum Newbie
Posts: 2
Joined: Sat Feb 27, 2010 5:56 pm

Re: Joining/querying unrelated tables - PLEASE HELP!

Post 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
Post Reply