SQL Server Performance Forum – Threads Archive
Joins/DedupesHi 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.
France. The finest wines. The tastiest cuisine. The most delicious women.
How do you define dupes? If you are looking for a unique list of all existing entries on the Application columns, the only way is to do this: SELECT DISTINCT Application1 FROM Table1
UNION SELECT DISTINCT Application2 FROM Table1
UNION SELECT DISTINCT Application1 FROM Table2
UNION SELECT DISTINCT Application2 FROM Table2 etc. etc. Ah, if only people would pay attention to normalization from the start …
Adriaan, you forgot, if you use union there is no need for distinct. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]
Mirko, good point (as always). Still, I’m intrigued whether the individual DISTINCT queries evaluate any quicker than the overall UNION (not ALL). Especially if there are indexes.