Sigh...

[DigitalBard]

I have never had to write a query (until now) that finds all of the values stored in one table that are not in another.

For example, I need to find all of the usernames in Table1 that do not exist in Table2. In ASP, I cheat and check in a DO/LOOP, but I have to do this in a query instead and I'm not sure how to go about it.

When I test for "tbl1.username <> tbl2.username" in the WHERE clause I'm not getting the correct results.

Can someone point me in the right direction?

[motormichael12]

What is the rest of your query?

Here is an example that should work:

SELECT *
FROM table1 tbl1, table2 tbl2
WHERE tbl1.username <> tbl2.username

[DigitalBard]

What is the rest of your query?

Here is an example that should work:

SELECT *
FROM table1 tbl1, table2 tbl2
WHERE tbl1.username <> tbl2.username

I get records from table2 which is what I don't want...

Looking at your example, I think I figured it out:

SELECT tbl1.username FROM tbl1, tbl2 WHERE tbl1.username <> tbl2.username

This should return only the records in tbl1 that do not exist in tbl2. I'm gonna give it a try.

[Twey]

Here's the obvious way to do it:

SELECT * FROM tbl1 WHERE username NOT IN (SELECT username FROM tbl2)

However, many versions of MySQL don't support terribly complex features like subselects, so you may have to do:

SELECT tbl1.* FROM tbl1 LEFT JOIN tbl2 ON tbl1.username = tbl2.username WHERE tbl2.username IS NULL