php mysql compare two columns return mismatches

Question

I have two columns in different product tables.

tblproduct1.partno is an old product list

tblproduct2.partno2 is a new one

Both partno columns should have identical model numbers but they don't.

When executing the below query, I get about 300 model numbers that don't match when comparing counts from both tables. tblproduct2 has 1955 records, the query below is 1638. I would expect it to return 1955.

SELECT COUNT(partno)
FROM tblproduct1
  INNER JOIN tblproduct2 ON partno = partno2

Is there a way I can list the model numbers that don't match?

Answer 1

select tblproduct1.partno from tblproduct1
   left join tblproduct2 on tblproduct1.partno = tblproduct2.partno2
   where tblproduct2.partno2 is null

shows tblproduct1.partno that have no matching tblproduct2.partno2 values

Answer 2

Actually stereofrogs query is correct. It works even when the table columns are defined as 'not null' I suspect you had the two tables mixed up when you ran the query.

that is because the LEFT JOIN always has all the rows from the left table in it. If the second table does not have a matching entry it will be displayed as NULL.

So as long as you have the table with more rows as the left (or the first) table the above query will produce the desired result.