Compare Lists

Displaying what is not in another list...

[inveni0]

I have two recordsets that, when queried, result in:

Recordset 1:
A
B
C
D

Recordset 2:

A
B
C
D
E
F
G

How can I compare these results and display only the items in set 2 that are not in set 1? For example:

Result:
E
F
G

Any help is, as always, greatly appreciated.

[inveni0]

For this could I use WHERE value1 != value2 in my query? I'm not at my work computer for a couple days, so I can't test it.

[sned]

If you're planning to do this in SQL, you could do something like

"SELECT table1.x FROM table1 WHERE table1.x NOT IN (SELECT table2.x FROM table2)"

I'm not completely sure if that's the correct syntax, and some databases don't support subqueries ...

If you wanted to do this after the queries are run, using arrays, you could use

array_merge [us3.php.net] and array_unique [us3.php.net]

-sned

[coopster]

Do you need both result sets? Or are you after just the mismatches? If so you can just let the database do the work.

[inveni0]

I'm after just the mismatches. How should I query the DB?

[coopster]

The query example sned provided will work. If you can't run subqueries you can always use a LEFT JOIN with IS NULL.

SELECT 
 table2.value 
 FROM table2 
 LEFT JOIN table1 ON table2.value = table1.value 
 WHERE table1.value IS NULL 
;

[inveni0]

On which version of PHP did they start allowing subqueries? Or is it the SQL version? I use mySQL.

[coopster]

MySQL >= 4.1