如何在连接字段中选择第一个表记录时不包含任何数据 [英] How to select first table record when joined field not contain any data

问题描述

我希望输出为打印机，打印机，null（选择至少前两个字段）

这个现有的查询和任何类型的连接

I want output as Printers,Printers,null (select at least first two fields)
with this very existing query and any kind of join

select a.acName,a.discription,s.quentity from #Accounts a
inner join #stock s on a.id = s.acId where addDate between '2001-11-01' and '2001-11-11' and a.id = 1

我的尝试：



这是场景

What I have tried:

this is the scenario

create table #Accounts(id int, acName nvarchar(50), discription nvarchar(128))
insert into #Accounts values (1, 'Printers', 'Printers')
insert into #Accounts values (2, 'HD', 'HD')
insert into #Accounts values (3, 'Mouse', 'Mouse')
insert into #Accounts values (4, 'Monitor', 'Monitor')
create table #stock(id int, acId int, quentity int,addDate datetime )
insert into #stock values (1,1,20,'2001-01-01')
insert into #stock values (1,2,10,'2001-10-01')
insert into #stock values (1,3,11,'2001-12-01')
insert into #stock values (1,4,5,'2001-11-01')

select a.acName,a.discription,s.quentity from #Accounts a
inner join #stock s on a.id = s.acId where addDate between '2001-11-01' and '2001-11-11' and a.id = 1

推荐答案

INNER JOIN 限制结果那些双方都有比赛的记录。要始终返回第一个表中的所有记录（以及匹配记录或第二个表中的NULL值），请使用 LEFT JOIN 。

INNER JOIN restricts the result to those records that have matches on both sides. To always return all records from first table (and matching records or NULL values from second table) use LEFT JOIN.

使用此：

Use this:

select TOP 1  a.acName,a.discription,IsNULL(CAST(s.quentity AS nvarchar(10)), 'null') from #Accounts a
left join #stock s on a.id = s.acId where addDate between '2001-11-01' and '2001-11-11' and a.id = 1

您需要使用LEFT OUTER JOIN 和来满足连接不返回任何内容的情况。像这样

You need to use LEFT OUTER JOIN and cater for scenarios where nothing is being returned by the join. Like this

select * from #Accounts A
left outer join #stock S on A.id=S.acId
where A.id = 1 
AND ((addDate between '2001-11-01' and '2001-11-11')
	OR addDate IS NULL)

这篇关于如何在连接字段中选择第一个表记录时不包含任何数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！