Matching tbl1.a with tbl2.a

[Anon]

Suppose you had two tables: tbl1 and tbl2. Both has unique(user). If I wanted to get a list of users that weren't in both tables, how would that be done in a single query? I know that tbl1.user = tbl2.user will give me the ones that ARE on both lists... tlb1.user != tbl2.user will give me lots of repeats (sizeof(tbl1) * sizeof(tbl2)-1). So how would you get this to work in one query? Or can PHP do something about this?

[Anon]

If you use MS SQL Server you could use FULL OUTER JOIN - it would give you all the list with those that are on both tables as well as those that are only in one of the tables (for these the value in the column representing the other table will be NULL). You will then need to sort through the results and display only those where you have a NULL value for one of the tables.

I am not sure what other RDBMS implements FULL OUTER JOIN.

HTH

Yaron

[Anon]

MySQL supports this kind of operation too.
Check out the manual.

In your query you can put
where table2.field=NULL

[Anon]

It's not in the manual... You can't do a full outer join (or whatever it's called) on MySQL as far as the manual is concerned, and whenever I do any kind of join, it gives me a multiplicative number of rows or just the number of rows in just one of the tables that doesn't help. What's going on here?

[Anon]

Well let's look at the manual anyway:

If there is no matching record for the right table in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find
records in a table that have no counterpart in another table:

 mysql> select table1.* from table1
            LEFT JOIN table2 ON table1.id=table2.id
            where table2.id is NULL;