Link to home
Start Free TrialLog in
Avatar of Steve Hougom
Steve HougomFlag for United States of America

asked on

sql query to find mismatches

This is probably pretty simple.

I have two querys.  One to find State = 'PQ' and one to find State = 'QC'

PQ query returns 724 rows.  QC query returns 704 rows.

I need to write a query to find the 20 PQ rows that dont match the QC ones.
Then im basically going to create 20 new rows for QC.  Because were getting rid of the PQ's.  So I need to find the rows where PQ and QC dont have the same citycode.  There basically all dupes except for the 20 PQ's that the QCs dont have a match for.

SELECT citycode, cityname, state, splc  
  FROM [dbo].[tbCompanyCityMaster]
  where state = 'PQ'
  (724 rows)

  SELECT citycode, cityname, state, splc 
  FROM [dbo].[tbCompanyCityMaster]
  where state = 'QC'
  (704 rows)

Open in new window

Avatar of ste5an
ste5an
Flag of Germany image

E.g.

SELECT citycode, cityname, splc  
FROM [dbo].[tbCompanyCityMaster]
WHERE state = 'PQ'
EXCEPT
SELECT citycode, cityname, splc 
FROM [dbo].[tbCompanyCityMaster]
WHERE state = 'QC'

Open in new window

EXCEPT is cool, here's another technique:
SELECT	a.*
FROM	[dbo].[tbCompanyCityMaster]	a
LEFT
JOIN	[dbo].[tbCompanyCityMaster]	b	ON	a.citycode = b.citycode
						AND	a.state = 'PQ'
						AND	b.state = 'QC'
WHERE	b.citycode IS NULL
                                            

Open in new window

If you need to see both missing citycodes -- that is, if QC could also have a citycode(s) that PQ does not -- you need to do a full outer join, or do two EXCEPTs and UNION ALL the results.
Avatar of Steve Hougom

ASKER

Scott

There are 722 states = PQ.  There are 704 rows with state = QC.

Issue is we need the 22 PQ ones to become QC.  PQ is an invalid state.

The real gold nugget is the splc.  So.   I need to figure out which of those 22 rows we dont have splcs for and make em QC rows.

Hope that helped a bit.  

The two scripts above did not work as id hoped.  Ste5an's query produced 99 non PQ or QC rows.

John Vidmar query produced every row in the table which is 44,000 rows.  Im still trying to figure out what went wrong with that one.  If i can think of a better way to explain what I need I will add it here maybe some screenshots of the table attributes as well.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
genious