Question: 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?

Answer: 
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]

```
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]

```