Finding out the unique value from two different tables with different column names

I am trying to find unique values by comparing the two tables resultsets. I have tried many solutions such as array merge, array unique but it has not worked hundred percent.

So to get to the problem.

Query 1:

SELECT field_ipe_case_numbers_value
FROM meiscontent_type_inbound_parts_entry
RIGHT JOIN meiscontent_field_ipe_case_numbers 
    ON meiscontent_type_inbound_parts_entry.nid = meiscontent_field_ipe_case_numbers.nid
WHERE field_ipe_invoice_number_nid =661
AND field_ipe_quality_status_value = "Usable Stock"
ORDER BY field_ipe_case_numbers_value DESC

Resultset 1:

10695
10694
10693
10692
10691
10690
10689
10688
10687
10686
10685
10684
10683
10682
10681
10680
10679
10678

Query 2:

SELECT field_pnp_case_no_value
FROM meiscontent_type_packing_note_parts
RIGHT JOIN meiscontent_field_pnp_case_no 
    ON meiscontent_type_packing_note_parts.nid = meiscontent_field_pnp_case_no.nid
WHERE field_pnp_invoice_no_nid =661
ORDER BY fiel开发者_StackOverflow中文版d_pnp_case_no_value DESC

Resultset 2:

10695
10694
10693
10692
10691
10690
10689
10688
10687
10686
10685
10684
10682
10681
10680
10679
10678

What is needed is the difference of these two resultsets. which should be 10683 but I am getting 10686.

Thanks for looking and as I am trying various solutions, hope there is somebody out there who has a better understanding of this problem and proposes a workable solution. Cant I just work it out using a SQL statement rather than getting the results in the ARRAY and try to do the operations as I am trying to do now.

If you're trying to just merge and de-duplicate the results of the two queries, then just do something like:

SELECT DISTINCT `value` FROM (
    SELECT `field_ipe_case_numbers_value` AS `value`
    FROM ...
    UNION
    SELECT `field_...` AS `value`
    FROM ...
)

If you are looking for a solution in php:

array_diff ($array1 , $array2); should be the right choice.

http://nz.php.net/manual/en/function.array-diff.php

HTH

On the SQL side, this should work:

SELECT field_ipe_case_numbers_value FROM <rest of first query>
EXCEPT
SELECT field_pnp_case_no_value FROM <rest of second query>

This should return everything in the first set that is not also found in the second set. (Reverse the order if necessary.)

That should be solvable in pure SQL also. A naive approach would be to just do:

select column from table a where column not in (select column from table b)
union
select column from table b where column not in (select column from table a)

Not sure what db you are using, but you could try:

(query1) EXCEPT (query2)

Edit: oops, I see it is mysql... you could try

query1 AND NOT EXISTS (query2)

although you will probably have to move the order by after the (query2)