Distinct - with 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 - with union

Author

Topic

aberbotimue
Starting Member

26 Posts

Posted - 2009-03-19 : 16:01:15

Hi guys..

My Uni days are coming back to me now.. I should have drunk less and learned more SQL..

I have this issue..

SELECT  Communities.Communityid AS comid, Communities.Community AS comname, Memberships.show 
FROM Communities 
Inner JOIN 
   Memberships ON Communities.Communityid = Memberships.CommunityId WHERE Memberships.UserName ='Aberbotimue'
UNION
select Communities.Communityid AS comid, Communities.Community AS comname , '0' as show from Communities

as the show col is showing somthing diferent for each, the distinct part of the union is giving me repeats in the comid and comname cos the show is different..


1	Male	0
1	Male	P
2	Female	0
3	Geek	0
3	Geek	P
4	Builder	0
5	Student	0
6	Mac Lover	0
7	iPhone Lover	0
8	Dog Lover	0
8	Dog Lover	P
9	Young at heart	0
9	Young at heart	P

gives what i want, but without the duplicate 1, 3, 8, 9..

is there a way to list all the items in the comunities table.
and if that community is in the memberships table for a specific user then show the [show] col for it.. but not twice as I get here

I hope that makes sense!

Aber...

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-19 : 16:03:35

Can you show your data from communities and memberships?

Terry

-- Procrastinate now!

aberbotimue
Starting Member

26 Posts

Posted - 2009-03-19 : 16:07:41

Memberships


Aberbotime	1	P
AnotherMember	1	P
Aberbotime	3	P
Aberbotime	8	P
Aberbotime	9	P
AnotherMember	7	F

communities


1	Male
2	Female
3	Geek
4	Builder
5	Student
6	Mac Lover
7	iPhone Lover
8	Dog Lover
9	Young at heart

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-19 : 17:24:26

Something like:

SELECT Communities.Communityid AS comid, Communities.Community AS comname, Memberships.show
FROM Communities
Inner JOIN
Memberships ON Communities.Communityid = Memberships.CommunityId WHERE Memberships.UserName ='Aberbotimue'
UNION
select C.Communityid AS comid, C.Community AS comname , '0' as show from Communities c
where C.Communityid not in (SELECT Communities.Communityid
FROM Communities Inner JOIN Memberships ON Communities.Communityid = Memberships.CommunityId WHERE Memberships.UserName ='Aberbotimue'
and Communities.Communityid = C.Communityid)

should work. You want to exclude the selected rows matched in the initial select.

Terry

-- Procrastinate now!

aberbotimue
Starting Member

26 Posts

Posted - 2009-03-19 : 18:39:43

definatly lost me...

it does remove the duplicates, but its taken out the ones that were in the first part of the UNION, rather than the ones that came as part of the second part of the UNION..

Any ideas?

aberbotimue
Starting Member

26 Posts

Posted - 2009-03-19 : 20:14:26

Take that back..

It does work,..

shows how much of a mental block I have with SQL..

Terry - Thank you so much.. I duno why I tryed it again.. just a sign I guess!!

I am sorry i responded faster than testing it.. This one has anoyed me for too long today.. I realy should stop writing things that require SQL, since i am so bad at it!

Thanks again..

Aber

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-20 : 09:49:23

quote:
Originally posted by aberbotimue

I realy should stop writing things that require SQL, since i am so bad at it!

TSQL is a pretty powerful tool but frustrating at times too - I speak from experience on the latter point. That's what makes these sites great, lot's of people with good skills (and, yes, some with not so good too!) willing to help where they can. Stick around, lots of good stuff here to help enhance your skillset. Glad I could help on this one.

Terry

-- Procrastinate now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources