Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Select Not Matching Data from Tables

Author  Topic 

aspnewbb
Starting Member

31 Posts

Posted - 2007-02-21 : 17:13:00
I have 2 tables, one has 400k barcodes and one has 150k...

what i need is a statement that will tell me what barcodes each table is missing when compared with the other.

I tried

SELECT table1.BARCODE from table1, table2 Where table1.barcode<>table2.barcode;

but it locks up the machine...

Im assuming this isnt the correct code?

Any help would be greatly appreciated!

Thank you!

aspnewbb
Starting Member

31 Posts

Posted - 2007-02-21 : 17:21:53
i also forgot to mention that both tables have duplicate barcodes if that makes a diference
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-21 : 17:27:10
What do you want to do with this data? Your select statement is going to return hundreds of thousands of rows of data, which is going to take forever! Not only will the client bog down due to the large result set being put in the client's memory, but the server will probably slow down too.

So rather than returning all of this data to the screen, you need to determine what you want to do with the data as you can't possibly review hundreds of thousands of rows of data. It would take an army to review that much data.

Tara Kizer
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 17:28:42
quote:
SELECT table1.BARCODE from table1, table2 Where table1.barcode<>table2.barcode;

but it locks up the machine...

You are doing a join that generate 400K x 150K records

use FULL OUTER JOIN

SELECT table1.BARCODE from table1 FULL OUTER JOIN table2 ON table1.barcode = table2.barcode



KH

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-21 : 17:37:15
quote:

SELECT table1.BARCODE from table1 FULL OUTER JOIN table2 ON table1.barcode = table2.barcode



That will have the same performance problem as his current query. Even if we come up with the proper query for him to "see" the missing data, it will still perform slow due to the amount of missing data. So he needs to decide what he wants to do with the data, such as insert the missing data into the proper table.

Tara Kizer
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 2007-02-22 : 08:02:43
regrettably i need to see the data...

I need to find out the what barcodes table2 has that table 1 does not so I can order them, and vise versa

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-22 : 08:04:04
have you tried the FULL OUTER JOIN ?

SELECT table1.barcode, table2.barcode from table1 FULL OUTER JOIN table2 ON table1.barcode = table2.barcode



KH

Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 2007-02-22 : 08:05:09
is this correct

SELECT table1.BARCODE from table1 FULL OUTER JOIN table2 ON table1.barcode = table2.barcode


Or do I need to use <> because i want ot see that data its missing (i dont think I under outer join)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-22 : 08:09:49
quote:
Originally posted by aspnewbb

i also forgot to mention that both tables have duplicate barcodes if that makes a diference



This does make a difference. what is the PK of each table? Can you give us some sample data and then, using that sample data, what the desired results would be? Keep the sample data small and simple and make sure that it covers all of the possibilities that you need to account for. If you data has duplicates, be sure that your sample data has duplicates, and so on. the better and clearer and more complete the information you can give us, the better we can help you.

In fact, even if you don't ask for us for help, you STILL should always use a small set of sample data covering all possibilities whenever you write pretty much any SQL statement that you are not sure about so you can quickly test it and verify that it works, without waiting for 100,000 rows of data to process and then pretty much having no way to verify that the results are correct.


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 2007-02-22 : 08:24:19
there is no PK in either table (we could create an autonumber if you think it makes a difference), its just a export from our in house inventory systems...

not alot of the barcodes are duplicates, proabably about 5%
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 2007-02-22 : 08:32:03
both tables are the same really

BARCODE | PRODUCTTYPE | PRODUCT NAME | PRICE
000123456789 | DVD | SQLTEAMRULES | $999999
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-22 : 08:38:50
see this example

declare @table1 table
(
BARCODE varchar(13),
PRODUCTTYPE varchar(15),
[PRODUCT NAME] varchar(15),
PRICE int
)

insert into @table1
select '012345678901', 'T1', 'SQLTEAMRULES', 999999 union all
select '123456789012', 'T1', 'SQL TEAM RULES', 999998 union all
select '234567890123', 'T1', 'SQL-TEAM-RULES', 999997


declare @table2 table
(
BARCODE varchar(13),
PRODUCTTYPE varchar(15),
[PRODUCT NAME] varchar(15),
PRICE int
)

insert into @table2
select '012345678000', 'T2', 'SQLTEAMRULES', 999999 union all
select '123456789012', 'T2', 'SQL TEAM RULES', 999998 union all
select '234567890000', 'T2', 'SQLTEAMRULES', 999997


select *
from @table1 t1 full outer join @table2 t2
on t1.BARCODE = t2.BARCODE
where t1.BARCODE is null
or t2.BARCODE is null

/*
BARCODE PRODUCTTYPE PRODUCT NAME PRICE BARCODE PRODUCTTYPE PRODUCT NAME PRICE
------------- --------------- --------------- ----------- ------------- --------------- --------------- -----------
NULL NULL NULL NULL 012345678000 T2 SQLTEAMRULES 999999
NULL NULL NULL NULL 234567890000 T2 SQLTEAMRULES 999997

012345678901 T1 SQLTEAMRULES 999999 NULL NULL NULL NULL
234567890123 T1 SQL-TEAM-RULES 999997 NULL NULL NULL NULL

*/



KH

Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 2007-02-22 : 08:53:29
I must have no iterated that im no pro sql baller... because I have been staring at that example for 10 minutes and don't even know where to start :D

select *
from @table1 t1 full outer join @table2 t2
// assuming this is some sort of virtual table, and t1 and t2 are just renaming table1 and table2.. i still dopnt think i //understand OUTer JOIN

on t1.BARCODE = t2.BARCODE

where t1.BARCODE is null
or t2.BARCODE is null

// i dont understand where the nulls are?
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 2007-02-22 : 08:57:46
i am very much trying to understand this, I thought it would be a simple statement in sql, i am so sorry

Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 2007-02-22 : 10:23:01
okay, do I dont have any of you trying to kill me I figured I would load it up in ACCESS and give it a shot...
Access doesnt suppose FULL OUTER JOIN (found that out the hard way :)


The ifrst thing I try to do is find out how WHICH barcodes Data (the smaller database of 150k records) has that CD1CONN (400k records, some dupes) does not have..

SO I ran this

SELECT Data.Barcode, CD1CONN.Barcode
FROM Data LEFT JOIN CD1Conn ON Data.Barcode = CD1CONN.Barcode;


The number returned was way larger then i expected, Does the query look correct to you?
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-22 : 10:54:58
Again, I repeat:
quote:

Can you give us some sample data and then, using that sample data, what the desired results would be? Keep the sample data small and simple and make sure that it covers all of the possibilities that you need to account for. If you data has duplicates, be sure that your sample data has duplicates, and so on. the better and clearer and more complete the information you can give us, the better we can help you.


1 single row of data in one table is not sample data. Sample data is a few rows in each table that accurately represent the larger set of data that you are working with, which will allow us and yourself to have some data to work with and to verify and to test. It is crucial skill for any programmer to understand the problem they are facing and to be able to simplify it and work on a small subset of data so that they can test and verify their code.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 2007-02-22 : 11:38:57
Sure thing, I was just trying to keep things simple and clean :)

Table CDCONN:

BARCODE OLDBARCODE TITLE CONFIG VNAME LNAME ANAME DESC CATALOG STREETDATE CUSTCUTOUT RTNCODE ONHAND LISTPRICE COST RCOST QTYONORDER
000000000000 689289403117 10820 21 Caroline Records Three Sixty Records Various Artists Black Diamond 3 Rock (Soft/Hard) 6/27/2000 3/25/2003 40 0 6.98 5.03 5.03 0
0000000000000 718752126120 21261 02 Caroline Records Rephlex Squarepusher Feed Me Weird Things Rock (Soft/Hard) 3/26/2003 40 0 18.98 13.24 13.24 0
000000000001 1 02 Local ATS A.T.S. Sepco Rock (Soft/Hard) 10 0 11.98 8.03 8.03 0
000000000004 04 02 Record-Rama Sound Archives Boilmaker Jazz Band Cosentino, Paul & The Boilermaker Jazz B Linger Awhile Jazz 10/27/1998 10 0 15.98 10.00 10.00 0
000000000009 009 02 Kid Glove Enterprises Kid Glove Enterprises Oteil & The Peacemakers Love Of A Lifetime Rock (Soft/Hard) 40 0 20.98 14.04 14.04 0
000000000011 011 02 Antilla Record Distributors L & L Records Lily Y Su Gran Trio Te Quiero Tanto Latin 40 0 15.98 10.08 10.08 0
000000000022 002 02 Glenn Pavone Moondog Records Cyclones, The Twist That Blues 6/4/1996 6/25/1999 40 0 13.98 8.50 8.50 0
000000000027 LKW027 00 Lumberjack Mordam Music Group, Inc. Coalition Records JR Ewing Perfect Drama, The Rock (Soft/Hard) 40 0 11.98 8.48 8.48 0
000000000044 002 04 Glenn Pavone Moondog Records Cyclones, The Twist That Blues 6/4/1996 6/25/1999 40 0 9.98 6.00 6.00 0
000000000045 45 AA Home Juke Box Service Sterling Title Strips Sterling Title Strip Blank 45 Title Strips Unclassified 40 0 15.98 11.00 11.00 0
000000000050 DECD050 02 Musictoday Grateful Dead Records Grateful Dead Dick's Picks 25 Rock (Soft/Hard) 11/22/2005 40 0 39.98 27.00 27.00 0
000000000058 58 02 Rincon Musical Musica Latina Rivera, Ismael Legend Latin-Pop 40 0 17.98 11.72 11.72 0
000000000087 087 02 Kid Glove Enterprises Kid Glove Enterprises Blue Floyd Blue Floyd: Allston, MA 2/1/00 Rock (Soft/Hard) 40 0 33.98 25.09 25.09 0
000000000088 088 02 Kid Glove Enterprises Kid Glove Enterprises Blue Floyd Blue Floyd: Mulligans Richmond, VA Rock (Soft/Hard) 40 0 39.98 30.49 30.49 0
000000000089 089 02 Kid Glove Enterprises Kid Glove Enterprises Blue Floyd Blue Floyd: Majestic Theater Detroit MI Rock (Soft/Hard) 40 0 39.98 30.49 30.49 0
000000000097 C97 02 Galaxy Music Promotions Galaxy Music Promotions Galaxy Of Hot Hits Country Vol. 97 Country 4/19/2004 10 0 13.98 9.98 9.98 0
000000000100 100 AA Something Special Entmnt Regis Sleeves "7"" Plastic Sleeves, 3 mil, 1000 count" Accessories 40 0 44.98 28.75 28.75 0
000000000102 102 02 Rego Irish Records & Tapes Project Children Mc Vivker, Marie Dreams Of My Land Ethnic 10 0 19.98 12.93 12.93 0
000000000106 AR450 AA Alpha Enterprises Inc Alpha Enterprises, Inc. Alpha Enterprises, Inc. Alpha cassette security holder Accessories 8/3/2005 40 0 1.1 0.64 0.64 0
000000000120 CB120 AA Eides Entertainment Empak Empak Cassette Holder Accessories 10 0 0.99 0.50 0.50 0
000000000126 126 02 Rincon Musical Kubaney Paniagua, Leonardo 14 Exitos Latin-Pop 40 0 13.98 9.10 9.10 0
000000000127 127 02 Rincon Musical Jose Luis Diaz, Maria Canto A Mi Pueblo Latin-Pop 40 0 14.98 9.63 9.63 0
000000000130 130 02 Kid Glove Enterprises Kid Glove Enterprises Allman Brothers Band, The Allman Brothers Band: Macon City Auditor Rock (Soft/Hard) 40 0 29.98 21.85 21.85 0
000000000144 144 02 Kid Glove Enterprises Kid Glove Enterprises Allman Brothers Band, The Allman Brothers Band 2004 Fox Box Insta Rock (Soft/Hard) 10 0 124.98 81.00 81.00 0





Table DATA:


BARCODE CONFIG TITLE DESC Field5 Field6 Field7
000000010030 9 BAGS UNLIMITED 12 INCH LP JACKET WITH HOLE 0.99 73 200
000000023160 3 VARIOUS 1999 DMC TECHNICS US DJ FINALS 22.98 2 0
000000024563 3 VARIOUS THE SHIGGAR FRAGGAR SHOW VOL.4 19.96 1 0
000000025638 21 GOLDEN PALOMINOS HEAVEN 5.98 13 0
000000025676 21 LL COOL J SAY WHAT 5.98 28 0
000000025744 21 CED-GEE LONG GEV 4.98 3 0
000000025768 9 BAGS UNLIMITED POLY 7 INCH BAGS (100 CT) 8.99 17 0
000000026130 3 VIDEO 1997 USA DJ CHAMPIONSHIP 24.96 1 0
000000027014 21 KALI WILD RYTHMATIC EXPLOSION 6.98 2 0
000000027090 21 ELIGH NIGHTLIFE 6.98 1 0
000000027168 21 SKITZOFRENIKS ON MY OWN SHIT 6.98 1 0
000000027748 3 DMC B-BOY FOUNDATIONS OFFICIAL B-BOY INSTRUCTIONAL V 22.98 3 0
000000027755 3 VARIOUS THE 1998 TECHNICS DMC US FINAL 24.96 1 0
000000028356 21 MADLIB MADLIB REMIXES 9.98 1 0
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-22 : 17:03:55
and what is the result that you want ?


KH

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-22 : 17:39:48
I'll say it one more time...

Even if we provide him with the correct query to see what data is missing, the query will never run fast as he is having it all dump to the screen. We're talking hundreds of thousands of rows of data. This will never complete in a short amount of time. He's got to figure out what he needs to do with all of this data rather than putting it all in memory on the client.

Tara Kizer
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-22 : 18:55:59
aspnewbb -- I asked you to keep things simple and clean, and you didn't!

By sample data, all you had to say was:

I have Table 1:

barcode
------
A
B
B
C
C


And Table 2:

BarCode
-------
B
D
D
E

And I would like to return this:

barcode result
------- ------
A 'Missing in Table 2'
D 'Missing in Table 1'
E 'Missing in Table 1'


If there are more columns in these tables that are important, or more things need to be considered, then show us in some simple sample data. This is called breaking down a problem into the important parts so that you can focus on them, and also creating a small subset of data so that you can quickly test things and verify your results.

Does this make sense?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-22 : 18:57:12
Also: What Tara said ....

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -