Distinct UNION? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Distinct UNION?

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-08 : 10:24:40

I have four tables:

table1
-------
code description
B Black sweater
C Cream sweater
R Red sweater

Table2
------
code description
G Green sweater
B Sweater, Black
W White sweater

Table3
------
code description
O orange sweater
P purple sweater

Table4
------
code description
M Mauve sweater
L Lilac sweater

I wish to merge all four tables into a new (master) table where there are no duplicate codes. After trying:

INSERT INTO table5
SELECT * FROM table1
UNION
SELECT * FROM table2
UNION
SELECT * FROM table3
UNION
SELECT * FROM table4

I got:

C Cream sweater
R Red sweater
G Green sweater
B Sweater, Black
B Black sweater
W White sweater
O orange sweater
P purple sweater
M Mauve sweater
L Lilac sweater

but I don't want duplicate codes so how can I correct me SQL query please?

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 10:46:59

Technically, those ARE distinct records. If your form is ALWAYS either [Color][space][Item] OR [Item][comma][space][Color], then you can write logic to deal with that.

You can use this for the above 2 forms:

Select
code,
Case
When CharIndex(', ', description) > 0 Then
SubString(description, CharIndex(', ', description) + 2, 500) +
' ' + Left(description, CharIndex(', ', description) - 1)
Else description
End As description
From Table1
Union
.
.
.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-08 : 11:04:47

OK, I understand. Thanks for your example.

If I were to ignore the description situation altogether, how would I select only one of any record(s) from the other tables whos code were the same for insertion into the new, merged, table? So if each table contained a record whos code was 'B'

i.e.

table1
------
code description
B Black sweater

table2
------
code description
B Sweater, black

table3
------
code description
B Sweater which is black

table4
------

code description
B Almost black sweater

The new table would just contain ONE of the 'B' code records ( I don't really care which, as long as I have a 'B' code item)?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2008-02-08 : 11:12:09

If you really don't care which description gets returned:

SELECT Code,  MIN(Description)
FROM ( SELECT Code, Description
       FROM   table1
       UNION
       SELECT Code, Description
       FROM   table2
       -- etc.
     ) AS t
GROUP BY Code

Mark

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-08 : 12:08:57

That nailed it.

Please accept my grateful thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources