SQL从两个有限制的表中选择

Basically, I have a two tables: management and pictures. I need a list with all properties from the management table. If pictures exist, the list only has to show the first picture (seq=1).

基本上，我有两张桌子:管理和图片。我需要一个包含管理表中所有属性的列表。如果图片存在，列表只需要显示第一张图片(seq=1)。

My problem: the list doesn't show the properties without pictures. How do I solve this?

我的问题是:列表没有图片就不会显示属性。我怎么解决这个问题?

I have the following tables:

我有以下表格:

tblMan

propid | city | lang | ...   
  1      Mol     NL        
  2      Olen    NL        
  3      Geel    NL       
  4      Ham     FR

tblPic

propid | lang | seq |  filename  
   1      NL     1      file11
   1      FR     1      file12
   1      NL     2      file13
   1      FR     2      file14
   1      NL     3      file15
   1      FR     3      file16
   3      NL     22     file17
   3      FR     22     file18
   3      NL     23     file19
   3      FR     23     file20
   3      NL     24     file21
   3      FR     24     file22

SELECT m.propid, m.city, p.filename
FROM  tblMan as m  
INNER JOIN tblProp as p ON m.propid = p.propid   
WHERE m.lang = 'NL' AND p.lang = 'NL' AND p.picture_index = '1'

The result is:

其结果是:

propid | city | filename   
  1      Mol     file11
  3      Geel    file17

He doesn't get the propid 2 (Olen) because there is no image!

他没有得到平淡的2(奥伦)，因为没有图像!

I want the result:

我想要的结果:

propid | city | filename   
  1      Mol     file11
  2      Olen    (Null or 0 or...) 
  3      Geel    file17

I tried subquery, union, concat... (@_@) with no result.

我尝试了subquery union concat。(@_@)没有结果。

Does anybody has an idee what kind of SQL statement I should use?

有人知道我应该使用哪种SQL语句吗?

Thanks for helping!

谢谢你的帮助!

3 个解决方案

#1

use LEFT JOIN since you want to display any records on table tblMan that matches your condition on the WHERE clause and move some filtering condition for table tblProp on the ON clause

使用左连接，因为您希望在表tblMan上显示与WHERE子句上的条件匹配的任何记录，并移动on子句上表tblProp的一些过滤条件

SELECT  m.propid, 
        m.city,  
        p.filename
FROM    tblMan as m  
        LEFT JOIN tblProp as p 
           ON m.propid = p.propid 
              AND p.lang = 'NL' 
              AND p.picture_index = '1'   
WHERE   m.lang = 'NL'

#2

Try this:

试试这个:

SELECT m.propid, 
       m.city, 
       p.filename 
FROM   tblman AS m 
       LEFT JOIN tblprop AS p 
              ON m.propid = p.propid 
                 AND p.lang = 'NL' 
                 AND p.picture_index = '1' 
WHERE  m.lang = 'NL'

#3

You need to use LEFT OUTER JOIN in place of INNER JOIN.

你需要用左外连接代替内连接。

#1