Joins/Dedupes - 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 2000 Forums

Transact-SQL (2000)

Joins/Dedupes

Author

Topic

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2007-01-04 : 12:49:47

Hi all,

I've got a series of 5 tables, each has 8 columns entitled Application 1, Application 2....Application 8. Each column has plenty of dupes, and blanks.

I need to merge these 40 columns into one column on one table, but with no dupes, and no blanks, THEN, I have to delete all entries with matching values in column 'apps' in table 'Filter'.

Any idea how the SQL should look? I imagine a "select distinct" and a join or two will be involved, but I'm an Admin guy...dev was never my strong point!!

Cheers all,

Jaybee.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-04 : 13:00:15

This will get you started, it will give you one list of all the values from the 40 columns with no dupes or blanks

SELECT Application1 AS Apps FROM table1 WHERE ISNULL(Application1, '') <> '' UNION
SELECT Application2 FROM table1 WHERE ISNULL(Application2, '') <> '' UNION
SELECT Application3 FROM table1 WHERE ISNULL(Application3, '') <> '' UNION
SELECT Application4 FROM table1 WHERE ISNULL(Application4, '') <> '' UNION
SELECT Application5 FROM table1 WHERE ISNULL(Application5, '') <> '' UNION
SELECT Application6 FROM table1 WHERE ISNULL(Application6, '') <> '' UNION
SELECT Application7 FROM table1 WHERE ISNULL(Application7, '') <> '' UNION
SELECT Application8 FROM table1 WHERE ISNULL(Application8, '') <> '' UNION
SELECT Application1 FROM table2 WHERE ISNULL(Application1, '') <> '' UNION
SELECT Application2 FROM table2 WHERE ISNULL(Application2, '') <> '' UNION
SELECT Application3 FROM table2 WHERE ISNULL(Application3, '') <> '' UNION
SELECT Application4 FROM table2 WHERE ISNULL(Application4, '') <> '' UNION
SELECT Application5 FROM table2 WHERE ISNULL(Application5, '') <> '' UNION
SELECT Application6 FROM table2 WHERE ISNULL(Application6, '') <> '' UNION
SELECT Application7 FROM table2 WHERE ISNULL(Application7, '') <> '' UNION
SELECT Application8 FROM table2 WHERE ISNULL(Application8, '') <> '' UNION
SELECT Application1 FROM table3 WHERE ISNULL(Application1, '') <> '' UNION
SELECT Application2 FROM table3 WHERE ISNULL(Application2, '') <> '' UNION
SELECT Application3 FROM table3 WHERE ISNULL(Application3, '') <> '' UNION
SELECT Application4 FROM table3 WHERE ISNULL(Application4, '') <> '' UNION
SELECT Application5 FROM table3 WHERE ISNULL(Application5, '') <> '' UNION
SELECT Application6 FROM table3 WHERE ISNULL(Application6, '') <> '' UNION
SELECT Application7 FROM table3 WHERE ISNULL(Application7, '') <> '' UNION
SELECT Application8 FROM table3 WHERE ISNULL(Application8, '') <> '' UNION
SELECT Application1 FROM table4 WHERE ISNULL(Application1, '') <> '' UNION
SELECT Application2 FROM table4 WHERE ISNULL(Application2, '') <> '' UNION
SELECT Application3 FROM table4 WHERE ISNULL(Application3, '') <> '' UNION
SELECT Application4 FROM table4 WHERE ISNULL(Application4, '') <> '' UNION
SELECT Application5 FROM table4 WHERE ISNULL(Application5, '') <> '' UNION
SELECT Application6 FROM table4 WHERE ISNULL(Application6, '') <> '' UNION
SELECT Application7 FROM table4 WHERE ISNULL(Application7, '') <> '' UNION
SELECT Application8 FROM table4 WHERE ISNULL(Application8, '') <> '' UNION
SELECT Application1 FROM table5 WHERE ISNULL(Application1, '') <> '' UNION
SELECT Application2 FROM table5 WHERE ISNULL(Application2, '') <> '' UNION
SELECT Application3 FROM table5 WHERE ISNULL(Application3, '') <> '' UNION
SELECT Application4 FROM table5 WHERE ISNULL(Application4, '') <> '' UNION
SELECT Application5 FROM table5 WHERE ISNULL(Application5, '') <> '' UNION
SELECT Application6 FROM table5 WHERE ISNULL(Application6, '') <> '' UNION
SELECT Application7 FROM table5 WHERE ISNULL(Application7, '') <> '' UNION
SELECT Application8 FROM table5 WHERE ISNULL(Application8, '') <> ''

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-01-04 : 13:05:19

Hi,
You can get the distinct set of values by doing a series of unions accross all columns across all tables, i.e.:


SELECT     t1.Application1 AS DedupedApplications
FROM       Table1 AS t1
WHERE      t1.Application1 IS NOT NULL
  AND      t1.Application1 <> ''

UNION
SELECT     t1.Application2
FROM       Table1 AS t1
WHERE      t1.Application2 IS NOT NULL
  AND      t1.Application2 <> ''

-- etc

UNION   
SELECT     t1.Application8
FROM       Table1 AS t1
WHERE      t1.Application8 IS NOT NULL
  AND      t1.Application8 <> ''
   
-- etc

UNION 
SELECT     t8.Application8
FROM       Table8 AS t8
WHERE      t8.Application8 IS NOT NULL
  AND      t8.Application8 <> ''

The UNION (without the Keyword ALL) will dedupe the list, whilst the where clauses constrain out the nulls / empty strings.

Mark

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2007-01-05 : 10:07:40

Hi Mark,

The merge/dedupe/null elimination works fine, any idea how I purge the results which have matching values in my 'exceptions' table?

Cheers,

Jaybee.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 11:59:12

DELETE Filter
FROM AppsTable
INNER JOIN Filter ON AppsTable.Apps = Filter.Apps

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources