Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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

0
Steve Hougom
Asked:
Steve Hougom
1 Solution
 
ste5anSenior DeveloperCommented:
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

0
 
John_VidmarCommented:
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

0
 
Scott PletcherSenior DBACommented:
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Steve HougomDeveloper IIAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
--INSERT INTO [dbo].[tbCompanyCityMaster] ( citycode, cityname, state, splc )
SELECT citycode, cityname, 'QC' AS state, splc
FROM [dbo].[tbCompanyCityMaster] ccm_pq
WHERE
    ccm_pq.state = 'PQ' AND
    NOT EXISTS (
        SELECT 1
        FROM [dbo].[tbCompanyCityMaster] ccm_qc
        WHERE
            ccm_qc.state = 'QC' AND
            ccm_qc.citycode = ccm_pq.citycode
    )
0
 
Steve HougomDeveloper IIAuthor Commented:
genious
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now