从表中获取所有记录，即使关联表中没有记录，两者都有条件

I have 2 tables as below.

我有2个表格如下。

Store table

store_id store_name
    1    abc
    2    bcd    
    3    cde

Purchase table

purchase_id  store_id   date
001     1           2013-07-10
002     1           2013-07-20

004     2           2013-07-20
005     2           2013-08-01

My current query is:

我目前的查询是:

SELECT ss.store_id,ss.store_name,tt.transaction_id FROM stores ss LEFT JOIN Purchase pp left join   on (ss.store_id=pp.store_id)

WHERE ss.store_id IN (1,3)  AND ((pp.date >= '2013-07-05') AND (pp.date <= '2013-08-05'))

Showing output like:

显示输出如:

Store_id store_name purchase_id

1        abc        001
2        abc        002

I need output like:

我需要输出如下:

Store_id store_name purchase_id

1        abc        001
2        abc        002
3        cde        NULL

Please suggest solution.Thank you.

请提出解决方案。谢谢。

Got the solution:

得到了解决方案:

SELECT ss.store_id,ss.store_name,tt.transaction_id FROM stores ss 
LEFT JOIN Purchase pp left join   on (ss.store_id=pp.store_id) 
AND ((pp.date >= '2013-07-05') AND (pp.date <= '2013-08-05'))
WHERE ss.store_id IN (1,3)

2 个解决方案

#1

You are limiting the query to search for the rows hvaing store_id (1,3)

您正在限制查询以搜索行hvaing store_id(1,3)

Removed the clause, Try this::

删除了该条款,试试这个::

SELECT 
ss.store_id,
ss.store_name,
tt.transaction_id 
FROM stores ss 
LEFT JOIN Purchase pp left join   on (ss.store_id=pp.store_id)

WHERE (pp.date >= '2013-07-05') AND (pp.date <= '2013-08-05')

#2

Your where clausule excludes NULL values. Add OR IS NULL for date and that id in where...

您的where clausule排除NULL值。为日期添加OR IS NULL,并在其中添加id ...

#1