Duplicates Again! UNION join - Remove records with column diff.

Dev Shed Forums Sponsor:

Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now!

September 9th, 2004, 02:03 PM

BaldNAskewed

Contributing User

Join Date: Sep 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 4

Duplicates Again! UNION join - Remove records with column diff.

Hello All,

We all were new at one point.... any help is appreciated.

Objective:

Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)

Reason:

I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.

My progress/where I'm stuck:

Here is my first query/union:

SELECT * FROM [Eds table]
UNION SELECT * FROM [Vickis table];

As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....

an example of duplicates that I must remove are as follows:

142301 - Product 5000 - 150# - S (Keep)
142031 - Product 5000 - 150# - "" <--- Blank (Remove)

I am trying to run another query on my first query results so I don't mess my first query up. Here it is:

SELECT DISTINCT [Prod #], [Prod Name], [Prod Description], [Product Type]
FROM [Combined Tables]
WHERE [Product Type]<>" ";

Please Help! Thank you in advance.

--------------------

5 minutes away from pulling my last one!

BaldNAskewed

September 9th, 2004, 02:41 PM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,766

Time spent in forums: 2 Weeks 4 Days 6 h 12 m 39 sec
Reputation Power: 37

Code:

SELECT * FROM [Eds table] et
where not exists
(select * from [Vickis table] vt
 where et.c1 = vt.c1
   and  et.c2 = vt.c2 
-- ... 
   and et.cn = vt.cn 
--  all columns that are needed to decide duplicity
 )
UNION all
SELECT * FROM [Vickis table] vt 
where not exists
(select * from [Eds table] et
 where et.c1 = vt.c1
   and  et.c2 = vt.c2 
-- ... 
   and et.cn = vt.cn 
--  all columns that are needed to decide duplicity
 )

September 9th, 2004, 03:24 PM

BaldNAskewed

Contributing User

Join Date: Sep 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 4

Getting Really Close!

Hi,

Thank you for the quick response!

Could you please explain to me what the not exists part of your UNION is doing? Maybe I can figure it out from there.

I believe I have followed everything you had mentioned, but unfortunately the output is returning 59,000 rows and it should be 49,000. Also, the majority of Product Type fields are blank, where as I know for a fact about 23,000 are coded.

In the meantime, more help is always appreciated. Thank you!

--------------------

5 minutes away from pulling the last one!

BaldNAskewed

September 9th, 2004, 03:44 PM

BaldNAskewed

Contributing User

Join Date: Sep 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 4

To clarify....

To help you understand the results I am trying to achieve:

142301 - Product 5000 - 150# - S (Keep)
142301 - Product 5000 - 150# - "" <--- Blank (Remove)

But if a product type has not been coded yet, I want it to remain on the list.

777777 - Product 7000 - 300# - "" <--- Blank (Keep)

This is a colaboration of 2 lists where they are coding the product type with "W, I, S, U, X". Eds sheet will have products coded that Vickis sheet won't and vice versa.

I am trying to combine the 2 so their colaborated efforts will show and only leave products left to be done.

Thank you in advance!

-----------------

5 minutes from pulling the last one!

September 9th, 2004, 04:30 PM

BaldNAskewed

Contributing User

Join Date: Sep 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 4

I'm still stuck, this should help you help me...

This informal code will hopefully spark some ideas, only I don't know the syntax to do what I provided below. Please help!

Select * from et

UNION?

Select * from vt

when/if Prod #= Prod #
and Product Type = Product Type

else if Prod # = Prod #
and Product Type <> Product Type
select * from where Product Type is not Null.

Please save my last hair!

------------------------

5 minutes away from pulling the last one!

BaldNAskewed

September 10th, 2004, 09:09 AM

BaldNAskewed

Contributing User

Join Date: Sep 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 4

Plucked it......

Well, I pulled my last hair out, I am so close! I had 2 classes on this stuff in college, I wish I could remember how to do this. Please help!

------------------------

Yep, completely bald, and I wax it too.

BaldNAskewed

September 10th, 2004, 10:18 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,766

Time spent in forums: 2 Weeks 4 Days 6 h 12 m 39 sec
Reputation Power: 37

The purpose of the not exists predicate is to filter those rows in eds table that are being handled by vicki (and vice versa in the second part of the union).

I made a simplified example.

Code:

                              M I M E R / B S Q L
                            Version 9.2.2A Beta Test


                               Username: sysadm
                               Password:

SQL>create table et(prodnr int, prodname char(10),status char);
SQL>create table vt(prodnr int, prodname char(10),status char);
SQL>
SQL>insert into et values(11,'xxx','');
SQL>insert into et values(12,'yyy','S');
SQL>insert into et values(13,'zzz','X');
SQL>insert into et values(14,'www','');
SQL>insert into et values(15,'hhh','');
SQL>
SQL>insert into vt values(11,'xxx','W');
SQL>insert into vt values(12,'yyy','');
SQL>insert into vt values(13,'zzz','');
SQL>insert into vt values(14,'www','');
SQL>insert into vt values(15,'hhh','I');
SQL>
SQL>select * from et
SQL&where not exists
SQL&(select * from vt
SQL&where vt.prodnr = et.prodnr
SQL&and vt.status /= '')
SQL&union
SQL&select * from vt
SQL&where not exists
SQL&(select * from et
SQL&where vt.prodnr = et.prodnr
SQL&and et.status /= '')
SQL&;
     PRODNR PRODNAME   STATUS
=========== ========== ======
         11    xxx             W
         12    yyy             S
         13    zzz             X
         14    www
         15    hhh             I

                  5 rows found

Have I misunderstood anything?

September 10th, 2004, 01:27 PM

BaldNAskewed

Contributing User

Join Date: Sep 2004

Posts: 31

Time spent in forums: < 1 sec
Reputation Power: 4

swampBoogie is da best!!!

Many thanks!!!!

It just took 1 modification with the exists and not exists. This is what I ended up with:

SELECT * FROM [et]
where NOT EXISTS
(select * from [vt]
where
[vt].[Prod #] = [et].[Prod #]
and [vt].[Product Type] = '')

UNION SELECT * FROM [vt]
where EXISTS
(select * from [et]
where
[vt].[Prod #] = [et].[Prod #]
and [et].[Product Type] = '');

---------------------------

I grew some hair back!!!

BaldNAskewed

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: