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 triedSELECT 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 |
|
|
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 |
|
|
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 recordsuse FULL OUTER JOINSELECT table1.BARCODE from table1 FULL OUTER JOIN table2 ON table1.barcode = table2.barcode KH |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
aspnewbb
Starting Member
31 Posts |
Posted - 2007-02-22 : 08:05:09
|
is this correctSELECT table1.BARCODE from table1 FULL OUTER JOIN table2 ON table1.barcode = table2.barcodeOr do I need to use <> because i want ot see that data its missing (i dont think I under outer join) |
|
|
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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
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% |
|
|
aspnewbb
Starting Member
31 Posts |
Posted - 2007-02-22 : 08:32:03
|
both tables are the same reallyBARCODE | PRODUCTTYPE | PRODUCT NAME | PRICE000123456789 | DVD | SQLTEAMRULES | $999999 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-22 : 08:38:50
|
see this exampledeclare @table1 table( BARCODE varchar(13), PRODUCTTYPE varchar(15), [PRODUCT NAME] varchar(15), PRICE int)insert into @table1select '012345678901', 'T1', 'SQLTEAMRULES', 999999 union allselect '123456789012', 'T1', 'SQL TEAM RULES', 999998 union allselect '234567890123', 'T1', 'SQL-TEAM-RULES', 999997declare @table2 table( BARCODE varchar(13), PRODUCTTYPE varchar(15), [PRODUCT NAME] varchar(15), PRICE int)insert into @table2select '012345678000', 'T2', 'SQLTEAMRULES', 999999 union allselect '123456789012', 'T2', 'SQL TEAM RULES', 999998 union allselect '234567890000', 'T2', 'SQLTEAMRULES', 999997select *from @table1 t1 full outer join @table2 t2 on t1.BARCODE = t2.BARCODEwhere t1.BARCODE is nullor t2.BARCODE is null/*BARCODE PRODUCTTYPE PRODUCT NAME PRICE BARCODE PRODUCTTYPE PRODUCT NAME PRICE ------------- --------------- --------------- ----------- ------------- --------------- --------------- ----------- NULL NULL NULL NULL 012345678000 T2 SQLTEAMRULES 999999NULL NULL NULL NULL 234567890000 T2 SQLTEAMRULES 999997012345678901 T1 SQLTEAMRULES 999999 NULL NULL NULL NULL234567890123 T1 SQL-TEAM-RULES 999997 NULL NULL NULL NULL*/ KH |
|
|
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 :Dselect *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.BARCODEwhere t1.BARCODE is nullor t2.BARCODE is null// i dont understand where the nulls are? |
|
|
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 |
|
|
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 thisSELECT Data.Barcode, CD1CONN.BarcodeFROM 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? |
|
|
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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
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 QTYONORDER000000000000 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 00000000000000 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 0000000000001 1 02 Local ATS A.T.S. Sepco Rock (Soft/Hard) 10 0 11.98 8.03 8.03 0000000000004 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 0000000000009 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 0000000000011 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 0000000000022 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 0000000000027 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 0000000000044 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 0000000000045 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 0000000000050 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 0000000000058 58 02 Rincon Musical Musica Latina Rivera, Ismael Legend Latin-Pop 40 0 17.98 11.72 11.72 0000000000087 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 0000000000088 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 0000000000089 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 0000000000097 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 0000000000100 100 AA Something Special Entmnt Regis Sleeves "7"" Plastic Sleeves, 3 mil, 1000 count" Accessories 40 0 44.98 28.75 28.75 0000000000102 102 02 Rego Irish Records & Tapes Project Children Mc Vivker, Marie Dreams Of My Land Ethnic 10 0 19.98 12.93 12.93 0000000000106 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 0000000000120 CB120 AA Eides Entertainment Empak Empak Cassette Holder Accessories 10 0 0.99 0.50 0.50 0000000000126 126 02 Rincon Musical Kubaney Paniagua, Leonardo 14 Exitos Latin-Pop 40 0 13.98 9.10 9.10 0000000000127 127 02 Rincon Musical Jose Luis Diaz, Maria Canto A Mi Pueblo Latin-Pop 40 0 14.98 9.63 9.63 0000000000130 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 0000000000144 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 0Table DATA:BARCODE CONFIG TITLE DESC Field5 Field6 Field7000000010030 9 BAGS UNLIMITED 12 INCH LP JACKET WITH HOLE 0.99 73 200000000023160 3 VARIOUS 1999 DMC TECHNICS US DJ FINALS 22.98 2 0000000024563 3 VARIOUS THE SHIGGAR FRAGGAR SHOW VOL.4 19.96 1 0000000025638 21 GOLDEN PALOMINOS HEAVEN 5.98 13 0000000025676 21 LL COOL J SAY WHAT 5.98 28 0000000025744 21 CED-GEE LONG GEV 4.98 3 0000000025768 9 BAGS UNLIMITED POLY 7 INCH BAGS (100 CT) 8.99 17 0000000026130 3 VIDEO 1997 USA DJ CHAMPIONSHIP 24.96 1 0000000027014 21 KALI WILD RYTHMATIC EXPLOSION 6.98 2 0000000027090 21 ELIGH NIGHTLIFE 6.98 1 0000000027168 21 SKITZOFRENIKS ON MY OWN SHIT 6.98 1 0000000027748 3 DMC B-BOY FOUNDATIONS OFFICIAL B-BOY INSTRUCTIONAL V 22.98 3 0000000027755 3 VARIOUS THE 1998 TECHNICS DMC US FINAL 24.96 1 0000000028356 21 MADLIB MADLIB REMIXES 9.98 1 0 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-22 : 17:03:55
|
and what is the result that you want ? KH |
|
|
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 |
|
|
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------AB BCCAnd Table 2:BarCode-------BDDEAnd 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?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
Previous Page&nsp;
Next Page
|