使用MySQL获取行是否存在? [英] Fetch column if row exists using MySQL?

问题描述

我有两个表photo和sale.

photo有两列id和url.

sale具有两列photoID和status.

问题是，photo表中的照片(行)不必在sale表中有记录，因此我们不知道照片是否在出售.

The thing is, a photo(row) in photo table doesn't have to have a record in sale table so we do not know if a photo is on sale or not.

即

photo

id | url

1  | http://...

2  | http://...

3  | http://...

sale

photoID | status

1       | 'sold'

3       | 'pending'

如您所见，具有id 2的照片在sale表中没有记录.我想做的是从photo表中提取所有照片，如果在sale表中有记录，我也希望在单个查询中提供status信息.我该如何实现?

As you can see, photo having id 2 doesn't have a record in sale table. What I want to do is to pull all photos from photo table, and if there is a record of it in sale table I want the status info too but in a single query. How can I achieve this ?

谢谢.

推荐答案

   SELECT p.*, s.status 
     FROM photo p
LEFT JOIN sale s 
       ON p.id = s.photoID

这篇关于使用MySQL获取行是否存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！