[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

sql query to find mismatches

Posted on 2014-03-26
6
Medium Priority
?
316 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 37

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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 

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

Get expert help—faster!

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

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 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