MySQL query Excluding rows

On this particular site I am using MySQL version 4.0.24 which rules out the EXISTS functionality.

The query I am trying to run has to output a list of results from a table but exclude the results where a field matches on 1 column as well as not matching on another column.

I thought the following query might work but it did not produce the required results;


select * from products p, products_extra pe
where p.products_id = pe.products_id
and NOT(p.products_flag = pe.products_flag)


Here is a listing of some test tables and below that is the required results;


products
---------

products_id title products_flag
1 A 123
2 B 124
3 C 125
4 D 126
5 A-1 1230


products_extra
--------------

pe_id products_id title products_flag
1 1 A-1 123
2 1 A-2 1230



required table
--------------

products_id title products_flag
1 A 123
2 B 124
3 C 125
4 D 126

 

I think I may have stumbled across some sort of a solution and I would be very grateful if someone could explain why the following query gave me the required result;

SELECT DISTINCT p.products_id, p.title, p.products_flag
FROM products p
LEFT JOIN products_extra pe ON NOT(p.products_flag = pe.products_flag) WHERE pe.products_flag = '1230'


I still need to specify a general term instead of '1230' to capture all results.

 

maybe u shud put p.products_flag = pe.products_flag without not as you want only those which are matching
like 123 in first n 123 in 2nd table ..so if they are equal it will give u desired iutput

 

Well, the matching column is the products ID so matching on the flag column wouldn't be giving me a subset of the result so I'm afraid that wouldn't work.

 

Just a guess because I can't test it but I think you'll get the point: