内连3桌 [英] Inner join with 3 tables

问题描述

我正在使用PHP和PDO，需要识别连接3个表的信息:

I'm working with PHP and PDO, and I need to recolect information joining 3 tables:

• 照片
• 相册
• 相册照片

该表具有以下结构:

照片:

photo_id  (int)
path      (varchar)
nick      (varchar)
date      (timestamp)

相册

album_id   (int)
album_name (varchar)
nick       (varchar)
date       (timestamp)

相册照片

album_id (int)
photo_id (int)
nick     (varchar)
date     (timestamp)

所以，我要显示所有相册，每张相册最多包含5张照片，用户昵称是所有者" .

So, I want to show all the albums with a max of 5 photos for each one, where the user nick is 'owner'.

如下所示:

album_name_1:
[photo_1]
[photo_2]
[photo_3]
[photo_4]
[photo_5]

album_name_2:
[photo_1]
[photo_2]
[photo_3]
[photo_4]
[photo_5]

我只知道可以使用INNER JOIN来制作类似的东西，但是我找不到如何用3张桌子来制作它.

I only know that something like this can be made with INNER JOIN, but I can't found how I can make it with 3 tables.

有例子或其他帮助我可以做到这一点吗?

There examples or other help that I can get to do this?

推荐答案

SELECT  a.*, c.date
FROM    Album a
        INNER JOIN Album_Photo b
            ON a.Album_ID = b.Album_ID
        INNER JOIN Photo c
            ON b.Photo_ID = c.Photo_ID
WHERE   c.Nick = 'owner' AND
        (
          SELECT COUNT(*) 
          FROM   album_photo d
          WHERE  b.album_id = d.album_id AND
                 d.nick = 'owner' AND
                 b.date >= d.date
        ) <= 2   // <<== change this value to 5

• SQLFiddle演示
• SQLFiddle Demo

这篇关于内连3桌的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！