Steve Hougom
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.
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)
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
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
genious
Open in new window