php mysql在同一个表中获取具有父ID的记录

I want to fetch the album types having photo or video. I don't need empty albums. Table stucture as follows

我想要获取包含照片或视频的相册类型。我不需要空白专辑。表结构如下

id  catID parentID   catName    type
1    1      0        location   album
2    2      0        color      album
3    3      1        USA        album
4    4      0        Illinois1  album 
5    4      3        Illinois   photo
6    5      3        Chicago    video
7    6      2        Black      photo
8    7      2        Red        photo

Here I want to fetch the rows which has child records. i.e Here Id 1,2 and 3 are set as parentid for 3,5,6,7,8th records. ID 4 doesn't have child records. SO I want to fetch the records Id 1,2,3

在这里,我想获取具有子记录的行。即在这里,Id 1,2和3被设置为3,5,6,7,8记录的parentid。 ID 4没有子记录。所以我想获取记录Id 1,2,3

I used the following query.

我使用了以下查询。

            select rm.* from media rm, media as tmp 
        inner join tmp on rm.id = tmp.parentID and (tmp.media_type = 'photo' OR tmp.media_type = 'video');

But its not working. As a result I want to fetch the album types having photo or video. I don't need empty albums.

但它不起作用。因此,我想要获取包含照片或视频的相册类型。我不需要空白专辑。

Please help me

请帮帮我

1 个解决方案

#1

You appear to have somehow confused implicit and explicit join syntax.

您似乎以某种方式混淆了隐式和显式连接语法。

Cleaning up your original query:-

清理原始查询: -

SELECT DISTINCT rm.id,
        rm.catID,
        rm.parentID,
        rm.catName,
        rm.type
FROM media rm
INNER JOIN media tmp  
ON rm.id = tmp.parentID 
AND tmp.media_type IN ('photo', 'video')

The distinct is required to only return each parent once even if they have multiple child records.

即使它们有多个子记录,也要求distinct只返回每个父项一次。

#1