[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql query to find mismatches

Posted on 2014-03-26
6
Medium Priority
?
313 Views
Last Modified: 2014-03-28
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
Comment
Question by:hougie40
6 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 39956910
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
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39957564
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39957620
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:hougie40
ID: 39960316
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39960348
--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
 

Author Closing Comment

by:hougie40
ID: 39962186
genious
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question