Query almost working, but now stuck

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

General SQL Server Forums

New to SQL Server Programming

Query almost working, but now stuck

Author

Topic

darrend
Starting Member

2 Posts

Posted - 2010-06-11 : 14:46:13

I'm pulling together a query from 3 different tables and it seems that it's very close to working (although I know there is no way this is the best way to do it). My current problem is I want to remove near duplicates. These things are hard to explain in words I guess, so here is the general query:

SELECT c1.f1, c1.f2, c1.f3, null as newcolumn, null as newcolumn2
FROM c1
WHERE c1.3 is not null

UNION

SELECT c1.f1, c1.f2, c1.f3, c2.f5 as newcolumn, null as newcolumn2
FROM c1 INNER JOIN c1 ON c1.f1 = c2.f1
WHERE c2.f5 > '0'

UNION
SELECT c1.f1, c1.f2, c1.f3, null as newcolumn, c3.f5 as newcolumn2
FROM c1 INNER JOIN c1 ON c1.f1 = c3.f1
WHERE c2.f5 = 's'

What I want to do now is remove any results from the first SELECT if they exist in either of the next two. Logically I want to do a JOIN of the first SELECT with the other two tables and have a WHERE like this:
WHERE c1.3 is not null AND (NOT (c2.f5 > '0') OR (c2.f5 = 's'))

but that totally doesn't work.

can somebody point me where I'm going wrong? Or if I need to provide more info.

I'm way too new to SQL and ready to pull some hair out.
thanks so much.

darrend
Starting Member

2 Posts

Posted - 2010-06-11 : 17:04:16

ok, let me try this in a more conceptual way. This isn't exactly the case, but should get at the overall question

I have 15k records in 1 master table. I have a subset of those records in two different tables, 233 in one and 5315 in another. I want to return a total of 15k records, but not the 5500 in the master table because I want those from the other 2 tables.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-12 : 11:46:36

just an idea.
select
coalesce(t3.col2,t2.col2,t1.col2) as col2
from mastertable t1
LEFT join table2 t2 on t2.pk=t1.pk
LEFT join table3 t3 on t3.pk=t1.pk

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources