0

I have two tables, photo_albums and images.

photo_albums

  • photo_albums_id: int(11)
  • cover_photo_id: int(11)

images

  • images_id: int(11)
  • img_name: varchar(255)
  • photo_albums_id: int(11)

I need to query the database to return all the photo albums, only once, plus the img_name of the cover_photo_id associated with each album. I don't want to return all the photos in each album. It also needs to work if there is no cover image associated with the album. This returns all the photo albums:

SELECT * FROM photo_albums ORDER BY photo_albums_id;

But I'm not sure how to get the following bit to work:

SELECT * FROM images WHERE image_id = cover_photo_id;
Jason
  • 9
  • 1

3 Answers3

0

It's a simple JOIN:

SELECT a.*, i.img_name
FROM photo_albums AS a
JOIN images AS i ON i.image_id = a.cover_photo_id
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Does this count as an SQL tutorial then? – Strawberry Sep 06 '17 at 22:58
  • @Strawberry It counts as a pity answer. I give them out once or twice a week. :) – Barmar Sep 06 '17 at 23:08
  • Hey guys, thanks for the pity answer. I don't do this for a living so excuse me for trying to learn a new skill. However, it doesn't solve the issue of returning all the albums. It only returns albums that have a cover_photo_id. – Jason Sep 07 '17 at 00:19
  • You don't learn a skill by getting others to do it for you. Read tutorials and start writing code. – Barmar Sep 07 '17 at 00:22
  • Jason - I suspect that only a minority of contributors to under this tag do mysql for a living (I don't); but I may be mistaken – Strawberry Sep 07 '17 at 00:25
0

You just need JOIN the two tables together. JOIN is one of the basic and most important things you can learn in SQL. There are 3 main types; INNER,OUTER and FULL. There's a cracking post HERE which explains the different types and the diagrams make it really easy to understand.

Once they are JOIN you can add your WHERE and ORDER too it, an example would be:

SELECT
    *
FROM table1 t1
INNER JOIN table2 t2 on t1.id = t2.id
WHERE t1.column2 = t2.column3
ORDER BY
    t2.column4

This uses an INNER JOIN so will only get the records that exist in both table.

dbajtr
  • 2,024
  • 2
  • 14
  • 22
0

Thanks guys, you all pointed me in the right direction. Barmar for the main bit and dbajtr for the link about join types. This did the trick:

SELECT a.*, i.img_name
FROM photo_albums 
AS a
LEFT OUTER JOIN images AS i ON i.images_id = a.cover_photo_id
WHERE a.status = '1'
Jason
  • 9
  • 1