CODEWITHSUNDEEP
sql-server-2008
Yves
Yves

Reputation: 12497

Help with Left Join

Below is my sql:

SELECT DISTINCT
    U.[IP Address],
    U.[Risk Rating],
    COUNT( U.[IP Address] ) AS UNIXIP,
    COUNT( U.[Risk Rating] ) RATINGCOUNT
FROM
    Exception AS E
LEFT JOIN
    tblUNIX_Archive AS U
        ON E.IP_address <> U.[IP Address]  -- Is using '<>' ok? instead of =
        AND (U.ScanDate BETWEEN '2011-01-22' AND '2011-02-18')
group by
    U.[Risk Rating],
    U.[IP Address]
ORDER BY
    U.[Risk Rating],
    U.[IP Address]

i only need to see the unique IP Address that are not in Exception table that are in tblUNIX_Archive table. I am getting multiple records. What am I doing wrong with the above sql?

Upvotes: 1

Views: 91

Answers (3)

legendofawesomeness
legendofawesomeness

Reputation: 2911

@Einacio, while your solution will work, I think having NOT IN in the where clause is going to hit the performance - may be even badly if the records are more.

@Kombucha, here is what you can try:

    SELECT DISTINCT
    U.[IP Address],
    U.[Risk Rating],
    COUNT( U.[IP Address] ) AS UNIXIP,
    COUNT( U.[Risk Rating] ) RATINGCOUNT
FROM
    tblUNIX_Archive AS U
LEFT JOIN
    Exception AS E
        ON E.IP_address = U.[IP Address]  -- = to is better 
        AND (U.ScanDate BETWEEN '2011-01-22' AND '2011-02-18')
where E.IP_adress is null
group by
    U.[Risk Rating],
    U.[IP Address]
ORDER BY
    U.[Risk Rating],
    U.[IP Address]

Update: noticed that the joined tables were in the wrong order...corrected that.

Upvotes: 2

HABO
HABO

Reputation: 15816

A left outer join will return rows from the left table, even if they are unmatched by rows on the right. Since you want all of the Unix IP addresses they should be on the left. (Or switch to a right outer join.) The WHERE clause rejects the matched rows.

SELECT DISTINCT
    U.[IP Address],
    U.[Risk Rating],
    COUNT( U.[IP Address] ) AS UNIXIP,
    COUNT( U.[Risk Rating] ) RATINGCOUNT
FROM
    tblUNIX_Archive AS U left outer join
    Exception AS E ON E.IP_address = U.[IP Address] AND
        (U.ScanDate BETWEEN '2011-01-22' AND '2011-02-18')
WHERE
    E.IP_address is NULL
group by
    U.[Risk Rating],
    U.[IP Address]
ORDER BY
    U.[Risk Rating],
    U.[IP Address]

Upvotes: 1

Einacio
Einacio

Reputation: 3532

to select the records in one table that aren't in another you don't have to use join, you have to use NOT IN

SELECT DISTINCT
    U.[IP Address],
    U.[Risk Rating],
    COUNT( U.[IP Address] ) AS UNIXIP,
    COUNT( U.[Risk Rating] ) RATINGCOUNT
FROM
    tblUNIX_Archive AS U
where  U.[IP Address] NOT IN( select E.IP_address  from Exception AS E)
        AND (U.ScanDate BETWEEN '2011-01-22' AND '2011-02-18')
group by
    U.[Risk Rating],
    U.[IP Address]
ORDER BY
    U.[Risk Rating],
    U.[IP Address]

Upvotes: 1

Related Questions