不显示所有记录的sql查询

Question

我有一个查询问题，例如这个我可以检索记录:

SELECT 
l.*, 
ll.f_cocacola as cocacola, 
 le.name AS deposit, 
 lp.price
FROM 
 KPC AS l, 
 KPC_cocacola AS ll, 
 KPC_deposit AS le, 
 KPC_price AS lp 
WHERE 
l.cod_deposit = le.id_deposit 
AND l.code = ll.code
AND lp.code = l.code
AND l.code_deposit = '002365' 

但现在我需要包含来自另一个表 -intranet- 的另一个日期，该表具有字段 code 和 url 然后需要从该表中显示 url 字段 .. 请记住，关系在 code 字段上。

所以我以这种方式更改查询:

SELECT l.*,ll.f_cocacola as cocacola, le.name AS deposit, 
lp.price, lintranet.url 
FROM tableX AS l, intranet_cocacola AS ll, tableX_deposit AS le, 
tableX_price AS lp, 
tableX_intranet as lintranet 
WHERE l.code_deposit = le.id_deposit
AND l.code = ll.code
AND lp.code = l.code 
AND l.code = lintranet.code
AND l.code_deposit = '456852147'

但问题是有些记录没有 url 并且如果不存在 url 那么此查询不会向我显示记录..好吧我需要什么，即使没有与记录关联的 url 我也想查看该记录，谢谢

Answer 1

使用 ANSI 连接语法重写您的查询并使用 LEFT JOIN:

SELECT  l.*, ll.f_cocacola as cocacola, le.name AS deposit, 
        lp.price, lintranet.url 
FROM    tableX AS l,
JOIN    intranet_cocacola AS ll
ON      ll.code = l.code
JOIN    tableX_deposit AS le
ON      le.id_deposit = l.code_deposit
JOIN    tableX_price AS lp
ON      lp.code = l.code
LEFT JOIN
        tableX_intranet as lintranet
ON      lintranet.code = l.code
WHERE   l.code_deposit = '456852147'