Solved

sql query to find mismatches

Posted on 2014-03-26
6
300 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 33

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 69

Expert Comment

by:ScottPletcher
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 69

Accepted Solution

by:
ScottPletcher earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now