SQL Right Excluding JOIN and WHERE issues

Question

I am trying to create a view with a right excluding join:

However there is a colon that i also want to do a WHERE on. In this case its:

WHERE table.colon != 'x';

for that reason i have tried:

WHERE A.key IS NULL AND table.colon != 'x';

but when i run it, no data is shown when i run it. Can anyone help me with this issue?

Thanks in advanced.

UPDATE, here is the query i am trying to run:

SELECT     dbo.DimServere.Servernavn, dbo.DimServere.Serverstatus, dbo.IderaPatchAnalyzer.IP_Adresse, dbo.IderaPatchAnalyzer.Release_, dbo.IderaPatchAnalyzer.Level_, 
                      dbo.IderaPatchAnalyzer.Edition_, dbo.IderaPatchAnalyzer.Build, dbo.IderaPatchAnalyzer.Updates_Available, dbo.IderaPatchAnalyzer.Supported_, 
                      dbo.IderaPatchAnalyzer.Support_Status
FROM         dbo.DimServere RIGHT OUTER JOIN
                      dbo.IderaPatchAnalyzer ON dbo.DimServere.TcpIpAddress = dbo.IderaPatchAnalyzer.IP_Adresse AND dbo.DimServere.Serverstatus <> 'Disposed/Retired'
WHERE     (dbo.DimServere.TcpIpAddress IS NULL)

Add a tag for the db type you are using. You can also consider using `MINUS` or `SUBTRACT` or `EXCEPT` depending on your flavour. — Glenn, Nov 18 '15 at 00:36
@Glenn I am using a SQL server 2008 R2 and using Microsoft SQL Server Management Studio 2012 — PuchuKing33, Nov 18 '15 at 00:38

score 1 · Answer 1 · answered Nov 18 '15 at 00:42

1

Move the condition into the JOIN clause.

SELECT *
FROM 
    Table1
    LEFT JOIN Table2
        ON Table1.Field = Table2.Field AND
        Table2.OtherField != 'x'
WHERE Table2.Field IS NULL

answered Nov 18 '15 at 00:42

Rabbit

507
3
9

hmm i have runned it and still no result – PuchuKing33 Nov 18 '15 at 00:54
What do you mean by no result? No rows come back at all? Or unexpected results are returned? – Rabbit Nov 18 '15 at 17:22
No rows come back at all. Just blank. As you can see i have updated the post with the query. Incl what you told me to try. – PuchuKing33 Nov 18 '15 at 17:24
That should only happen if for every tcp ip address in dbo.DimServere there is a matching tcp ip address in dbo.IderaPatchAnalyzer and none of them have a exactly equal to 'Disposed/Retired'. I notice there's a slash in the status, is that 2 different statuses or just one status? – Rabbit Nov 18 '15 at 17:34
There are a match. When i remove where != 'disposed/retired' it works. And its one status. – PuchuKing33 Nov 18 '15 at 17:37
Why do you return fields in the SELECT clause from the dbo.DimServere table? If the query is working like you expect, those fields will always be null. – Rabbit Nov 18 '15 at 17:51

Vamsi Prabhala · Answer 2 · 2015-11-18T01:07:30.213

0

The idea is correct. However, i guess there are null values in the dbo.DimServere.Serverstatus column. Because you are using an comparison operator, this condition always fails (comparison with null is unknown) and the join condition is not satisfied, (because you are ANDing it with another condition) and you don't get any results.

To overcome this, use isnull(dbo.DimServere.Serverstatus, 'somevalue') in the join condition.

SELECT dbo.DimServere.Servernavn, dbo.DimServere.Serverstatus,
dbo.IderaPatchAnalyzer.IP_Adresse, dbo.IderaPatchAnalyzer.Release_,
dbo.IderaPatchAnalyzer.Level_, dbo.IderaPatchAnalyzer.Edition_, 
dbo.IderaPatchAnalyzer.Build, dbo.IderaPatchAnalyzer.Updates_Available,    
dbo.IderaPatchAnalyzer.Supported_, dbo.IderaPatchAnalyzer.Support_Status
FROM dbo.DimServere RIGHT OUTER JOIN
dbo.IderaPatchAnalyzer ON dbo.DimServere.TcpIpAddress = dbo.IderaPatchAnalyzer.IP_Adresse 
AND isnull(dbo.DimServere.Serverstatus, 'somevalue') <> 'Disposed/Retired' 
WHERE dbo.DimServere.TcpIpAddress IS NULL

edited Nov 18 '15 at 01:07

answered Nov 18 '15 at 01:03

Vamsi Prabhala

48,685
4
36
58

Negative, i just ran SELECT * FROM [SIT-DVH].[dbo].[DimServere] WHERE Serverstatus IS NULL; and no rows showed up. – PuchuKing33 Nov 18 '15 at 01:06
have you tried it without the `where` clause?just to check – Vamsi Prabhala Nov 18 '15 at 01:08
Yes i have tried the query without AND dbo.DimServere.Serverstatus <> 'Disposed/Retired', then it works. But i need only to see data from the right side. – PuchuKing33 Nov 18 '15 at 01:11
Sorry i meant, that i need to see only the right side without the Disposed/Retired values in the Serverstatus – PuchuKing33 Nov 18 '15 at 01:22

SQL Right Excluding JOIN and WHERE issues

2 Answers2