当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Left join does not return ull values

问题描述:

I have two tables:

AppWindowsEvent:

CREATE TABLE [AppWindowsEvent]

(

[idAppWindowEvent] INT IDENTITY(1,1)

, [idAppWindow] INT

, [idEventType] INT

, [Order] INT

, CONSTRAINT PK_idAppWindowEvent PRIMARY KEY ([idAppWindowEvent])

, CONSTRAINT FK_idAppWindowEvent_AppWindow FOREIGN KEY ([idAppWindow]) REFERENCES [AppWindow]([idAppWindow])

, CONSTRAINT FK_idAppWindowEvent_EventType FOREIGN KEY ([idEventType]) REFERENCES [EventType]([idEventType])

)

Event:

CREATE TABLE [Event]

(

[idEvent] [INT] IDENTITY(1,1) NOT NULL

, [idEventType] [INT] NOT NULL

, [idEntity] [INT] NOT NULL

, CONSTRAINT PK_IdEvent PRIMARY KEY([idEvent])

, CONSTRAINT [FK_Event_EventType] FOREIGN KEY([idEventType]) REFERENCES [EventType] ([idEventType])

)

When i run this query:

SELECT

*

FROM

AppWindowsEvent AWE

LEFT JOIN Event E ON AWE.idEventType = E.idEventType

WHERE

AWE.idMill = 1

AND AWE.idAppWindow = 1

ORDER BY

AWE.[Order] ASC

The result: not return nulls.

And when i run this

SELECT

*

FROM

AppWindowsEvent AWE

LEFT JOIN Event E ON AWE.idEventType = E.idEventType

AND E.[idEntity] = 1234

WHERE

AWE.idMill = 1

AND AWE.idAppWindow = 1

ORDER BY

AWE.[Order] ASC

Result: return nulls.

NOTE:

I need the entire set of data that are and are not already configured, in case you want a specific set of events, in the AND of ON can be filtered by specific idEntity of the Event table and the result returns well, but only for that idEntity, in my case I need all idEntity.

网友答案:

Try this

SELECT *
FROM
AppWindowsEvent AWE
LEFT JOIN Event E ON AWE.idEventType = E.idEventType 
WHERE
AWE.idMill = 1
AND AWE.idAppWindow = 1
AND E.[idEntity] = 1234
ORDER BY
AWE.[Order] ASC

Or if you doesn't want appear null valor in second table, you can use Inner Join instead Left Join

SELECT *
FROM
AppWindowsEvent AWE
Inner JOIN Event E ON AWE.idEventType = E.idEventType 
AND E.[idEntity] = 1234
WHERE
AWE.idMill = 1
AND AWE.idAppWindow = 1
ORDER BY
AWE.[Order] ASC
分享给朋友:
您可能感兴趣的文章:
随机阅读: