0

I have 3 tables.

images - image_id .. etc

users - user_id .. etc

favs - user_id, image_id

So now I want when user log into his account and click on button My favs to show all his favs. What would be the query to mysql here?

John
  • 171
  • 1
  • 2
  • 12
  • Did you try building a query? If yes, post it. – Ravinder Reddy Jul 29 '15 at 12:36
  • this is either a duplicate of [combine-two-tables-that-have-no-common-fields](http://stackoverflow.com/questions/1198124/combine-two-tables-that-have-no-common-fields) or [sql-inner-join-more-than-two-tables](http://stackoverflow.com/questions/14995000/sql-inner-join-more-than-two-tables) – Steffen Winkler Jul 29 '15 at 12:40
  • I'm not. I don't know how exactly. What JOIN to use, why to use that join, how exactly joins work. What I want? So I will need to select all `images` that are in `favs` and where `user_id=1`? – John Jul 29 '15 at 12:40

1 Answers1

2
select images.image_id, ... from images
inner join favs on images.image_id = favs.image_id
inner join users on users.user_id = favs.user_id
where users.user_id = [...]

If you don't need any data from users then the last join is not necessary and you can change the last line to where favs.user_id = [...]

This might help you understand which joins do what: enter image description here

PhilippD
  • 111
  • 1
  • 4
  • i've got error on this join `inner join favs on images.user_id = favs.image_id` ->` Unknown column 'images.user_id' in 'on clause'` .. I don't have user_id column in table `images` – John Jul 29 '15 at 12:44
  • **@John**: change `images.user_id = favs.image_id` to `images.image_id = favs.image_id` – Ravinder Reddy Jul 29 '15 at 12:46
  • Sorry, I've edited the query to `inner join favs on images.image_id = favs.image_id` – PhilippD Jul 29 '15 at 12:48
  • Tried that and didn't work also. But like this is working `images.image_id = favs.user_id` – John Jul 29 '15 at 12:48