mysql - 针对其他表过滤具有多个内部联接的一个表

Question

我有四个表，Photo、Event、News、Spot，而 Photo 是我要检查与其他表有关系的记录的表。

照片有以下结构:

id
rel_model -> one of "news", "spot" and "event"
rel_id    -> id of the related record in rel_model table 
...

除照片外的表格不断更新，一些记录被删除。我想过滤照片以获取与其他表上现有记录相关的记录。

我尝试了以下

select 
    count(*)
from
    Photo
        inner join Event ON (rel_id = Event.id and rel_model="event") 
        inner join News ON (rel_id = News.id and rel_model="news")
        inner join Spot ON (rel_id = Spot.id and rel_model="spot"); 

但我得到 0 个结果，其中仅使用一个内部连接尝试检查单个表

select 
    count(*)
from
    Photo
        inner join Event ON (rel_id = Event.id and rel_model="event") ;

我需要在内部连接之间添加一些 and-or 逻辑，有点想不通。

如何获取与其他表仍然没有中断关系的照片？

Answer 1

你可以使用这个查询

select 
    count(*)
from Photo as P
where
    P.rel_model = "event" and P.rel_id in (select T.id from Event as T) or
    P.rel_model = "news" and P.rel_id in (select T.id from News as T) or
    P.rel_model = "spot" and P.rel_id in (select T.id from Spot as T)

如果你想改变你的查询，你应该使用left outer join:

select 
    count(*)
from Photo as P
    left outer join Event ON (rel_id = Event.id and rel_model="event") 
    left outer join News ON (rel_id = News.id and rel_model="news")
    left outer join Spot ON (rel_id = Spot.id and rel_model="spot")
where News.id is not null or Spot.id is not null or Event.id is not null

您的查询返回空行，因为您试图将同一行与所有三个表连接起来，但您的连接条件只匹配一个，因此其他两个内部连接会消除您的行。