Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SELECT question Expand / Collapse
Author
Message
Posted Monday, December 9, 2013 1:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:09 AM
Points: 37, Visits: 158
I have 2 tables. Each one has the following columns: SSN, PKT and FICE_NBR.

I need to be able to retrieve the rows from table A where the combo of SSN and PKT and FICE_NBR in table A do not equal the combo of SSN and PKT and FICE_NBR in table B. Each of the tables has approx. 4 million rows.

I need to be able to include all of the columns from table A in the result set. The columns from table B are not required in the result set.

Any thoughts on the best way to write the SQL for this query?

Any help would be appreciated.
Post #1521283
Posted Monday, December 9, 2013 1:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:09 AM
Points: 37, Visits: 158
I forgot to mention that I need to exclude duplicate rows from the result set.

Thanks.
Post #1521284
Posted Monday, December 9, 2013 3:17 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 10:27 AM
Points: 934, Visits: 160
gary.morey (12/9/2013)
I have 2 tables. Each one has the following columns: SSN, PKT and FICE_NBR.

I need to be able to retrieve the rows from table A where the combo of SSN and PKT and FICE_NBR in table A do not equal the combo of SSN and PKT and FICE_NBR in table B. Each of the tables has approx. 4 million rows.

I need to be able to include all of the columns from table A in the result set. The columns from table B are not required in the result set.

Any thoughts on the best way to write the SQL for this query?

Any help would be appreciated.


you can go like this:
SELECT SSN , PKT , FICE_NBR
FROM A
WHERE SSN + PKT + FICE_NBR
NOT IN (
SELECT SSN + PKT + FICE_NBR
FROM B
)

and for the duplicates a DISTINCT is enough i think
like:
SELECT DISTINCT SSN , PKT , FICE_NBR
FROM BLAH ...



Aram Koukia: http://www.koukia.ca
Post #1521322
Posted Monday, December 9, 2013 4:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 3,354, Visits: 7,251
You could use EXCEPT

SELECT SSN , PKT , FICE_NBR
FROM A
EXCEPT
SELECT SSN , PKT , FICE_NBR
FROM B




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521343
Posted Monday, December 9, 2013 5:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:41 PM
Points: 23,033, Visits: 31,555
Luis Cazares (12/9/2013)
You could use EXCEPT

SELECT SSN , PKT , FICE_NBR
FROM A
EXCEPT
SELECT SSN , PKT , FICE_NBR
FROM B




This?


with baserecs as (
SELECT SSN , PKT , FICE_NBR
FROM TableA
EXCEPT
SELECT SSN , PKT , FICE_NBR
FROM TableB
)
select a.*
from TableA a inner join baserecs br on (a.SSN = br.SSN and a.PKT = br.PKT and a.FICE_NBR = br.FICE_NBR);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1521363
Posted Tuesday, December 10, 2013 1:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
SELECT DISTINCT *
FROM Tablea a
WHERE NOT EXISTS (
SELECT 1
FROM Tableb b
WHERE b.SSN = a.SSN
AND b.PKT = a.PKT
AND b.FICE_NBR = a.FICE_NBR
)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1521422
Posted Tuesday, December 10, 2013 1:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
Aram Koukia (12/9/2013)
gary.morey (12/9/2013)
I have 2 tables. Each one has the following columns: SSN, PKT and FICE_NBR.

I need to be able to retrieve the rows from table A where the combo of SSN and PKT and FICE_NBR in table A do not equal the combo of SSN and PKT and FICE_NBR in table B. Each of the tables has approx. 4 million rows.

I need to be able to include all of the columns from table A in the result set. The columns from table B are not required in the result set.

Any thoughts on the best way to write the SQL for this query?

Any help would be appreciated.


you can go like this:
SELECT SSN , PKT , FICE_NBR
FROM A
WHERE SSN + PKT + FICE_NBR
NOT IN (
SELECT SSN + PKT + FICE_NBR
FROM B
)

and for the duplicates a DISTINCT is enough i think
like:
SELECT DISTINCT SSN , PKT , FICE_NBR
FROM BLAH ...



1. A row in table a with SSN = 1 and PKT = 2 would match a row in table b with SSN = 2 and PKT = 1 and matching FICE_NBR. This method is not recommended where the matching columns can generate an arithmetic addition.
It can break with character data too, less likely if an unexpected character e.g. '|' separates the columns.
2. It's not SARGable. In the unlikely event that it works, performance will always be poor.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1521423
Posted Tuesday, December 10, 2013 10:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 10:27 AM
Points: 934, Visits: 160

Yes, That's right
In cases like this, when I concat column data together I always use a special character as a separator to make sure that kind of conflict won't happen.



Aram Koukia: http://www.koukia.ca
Post #1521599
Posted Tuesday, December 10, 2013 5:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
Personally, I'd like to know why in the hell anyone would be storing SSNs in plain text!

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1521717
Posted Wednesday, December 11, 2013 6:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 7,051, Visits: 6,807
Jeff Moden (12/10/2013)
Personally, I'd like to know why in the hell anyone would be storing SSNs in plain text!


Are you assuming combo = combination = concatenation



Far away is close at hand in the images of elsewhere.

Anon.

Post #1521868
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse