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: 
@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.
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]

```