I have a table that stores each location a user is watching. the table is called usercity and is pictured below an example row. I need to SELECT all rows WHERE user_id='1' , and then retrieve each location_id 's record from the location table, which is pictured below: I am not really sure where to start here, but here's my lame attempt: SELECT b.* FROM usercity a JOIN location b ON a.location_id=b.id WHERE user_id='1' Edit My query Works with one record. not sure if it will work with multiple rows.

Reputation: 4742

Select Multiple rows then join for each row

I have a table that stores each location a user is watching. the table is called usercity and is pictured below an example row.

enter image description here

I need to SELECT all rows WHERE user_id='1', and then retrieve each location_id's record from the location table, which is pictured below:

enter image description here

I am not really sure where to start here, but here's my lame attempt:

   SELECT b.* FROM usercity a JOIN location b ON a.location_id=b.id WHERE user_id='1'

Edit My query Works with one record. not sure if it will work with multiple rows.

Upvotes: 0

Reputation: 44874

If its one-to-one relation then you can use inner join

select
uc.*,
l.* from usercity uc
join location l on l.location_id = uc.location_id
where l.user_id=:uid

Upvotes: 1

Reputation: 6854

try below-

select a.*, b.* 
from usercity a JOIN location b ON a.location_id=b.id
WHERE user_id=:uid

Upvotes: 1

Reputation: 4742

well, i kept playing with it and got what i want:

SELECT b.* FROM usercity a JOIN location b ON a.location_id=b.id WHERE user_id='1'

I guess SQL is one of those things you learn through practice.

Upvotes: 0