CODEWITHSUNDEEP
mysqlsqlselectjoin
Release
Release

Reputation: 1167

SELECT rows from a table joining only one row of another table

I Have two tables: Home(ID, name) and 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.

Now i'd like to SELECT * FROM Home merging for each home its splash, if exists, else splash returns 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

Upvotes: 3

Views: 1605

Answers (2)

Bud Damyanov
Bud Damyanov

Reputation: 31919

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

Upvotes: 1

Florin Ghita
Florin Ghita

Reputation: 17643

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

Upvotes: 3

Related Questions