从只连接另一个表的一行的表中选择行

I Have two tables: Home(ID, name) and Photos(ID, homeid, filename, splash)

我有两个表:Home(ID, name)和Photos(ID, homeid, filename, splash)

Home primary key is ID, Photos primary key is ID. A Home can have multiple photos. Splash can be 0 or 1 and, for each HomeID, at most one photo can have splash=1.

Home主键是ID, Photos主键是ID, Home可以有多个照片。Splash = 0或1，对于每个HomeID，最多有一张照片的Splash =1。

Now i'd like to SELECT * FROM Home merging for each home its splash, if exists, else splash returns null.

现在我想从Home merge为每个家庭选择*，如果存在，否则splash返回null。

How can i do?

我怎么能做什么?

Here's an example of the tables:

这里有一个表格的例子:

Home
ID     name
1      home1
2      home3252
3      home5326

Photos
ID     homeid       filename       splash
1      1            asda.jpg       0
2      1            aspg.jpg       1
3      2            nasf.jpg       0
4      2            qfqj.jpg       1
5      3            vnas.jpg       0
6      3            nfao.jpg       0

2 个解决方案

#1

select h.name, p.filename
from Home h
left join Photos p on (h.id = p.homeid and p.splash = 1)

#2

The answer is in your question, use LEFT OUTER JOIN MySQL syntax...

答案在你的问题中，使用左外连接MySQL语法…