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?

user187291 · Accepted Answer · 2010-02-10 13:17:51Z

2

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

edited Feb 10, 2010 at 13:17

answered Feb 10, 2010 at 11:55

user187291

54.1k19 gold badges98 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jimsmith Over a year ago

the partno2 isn't null, it just doesn't match

jimsmith Over a year ago

sorry, I can see the query works thanks for the answer, i have asked e4c5, but I'll reinstate the question here: is there a way to see the model number? tblproduct2.partno2 returns null

e4c5 · Accepted Answer · 2010-02-10 15:31:36Z

0

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.

answered Feb 10, 2010 at 15:31

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

2 Comments

jimsmith Over a year ago

Yes it works, thanks for the explanation, how do I list the models in the null column? I need to compare them

e4c5 Over a year ago

you can add a second column to the query so that you have something to compare against eg: select tblproduct1.partno, tblproduct.partName ... all the best

Collectives™ on Stack Overflow

php mysql compare two columns return mismatches

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related