How to get distinct values - 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

General SQL Server Forums

New to SQL Server Programming

How to get distinct values

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-06-01 : 09:05:14

I'm having trouble getting distinct items from 2 tables. I've tried distinct and group by but not helping. Table 1 is the lookup table and table 2 is the main table.

Table 1 luURLPath

lurlpathid[pk] sName
1 /Forums/forums/default.aspx
2 /Forums/forums/Test1.aspx
4 /Forums/forums/Test2.aspx

Table 2 strHelpManager

lhelpid ldomainid lurlpathid[FK] llanguageid scontent
1 37 1 25 this is in english
7 37 2 25 NULL
19 37 1 26 this is in spanish
21 37 1 27 this is in finnish

I want to display sName from table 1 where the ldomainid is 37 so the expected result is:

/Forums/forums/default.aspx
/Forums/forums/Test1.aspx

However I get the following output when using the code below:

/Forums/forums/default.aspx
/Forums/forums/default.aspx
/Forums/forums/default.aspx
/Forums/forums/Test1.aspx


SELECT [lHelpID], (SELECT sName from [luURLPath] where ([lURLPathID] = strHelpManager.lURLPathID)) as URLName 
FROM [strHelpManager] 
WHERE ([lDomainID] = 37) 
ORDER BY URLName

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-06-01 : 09:11:39

select sName from [luURLPath] where
lurlpathid in
(
select lurlpathid from [strHelpManager]
WHERE ([lDomainID] = 37)
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-01 : 13:24:32

or use join


select sName from [luURLPath] u
INNER JOIN 
(
select lurlpathid 
from [strHelpManager] 
GROUP BY lurlpathid 
HAVING SUM(CASE WHEN ldomainid=37 THEN 1 ELSE 0 END) > 0
)m
ON m.lurlpathid =u.lurlpathid

insanepaul
Posting Yak Master

178 Posts

Posted - 2009-06-08 : 04:32:54

quote:
Originally posted by LoztInSpace

select sName from [luURLPath] where
lurlpathid in
(
select lurlpathid from [strHelpManager]
WHERE ([lDomainID] = 37)
)

Thanks guys...I guess both solutions are as good as each other but as a sql beginner the quoted one looks simpler to implement than the join

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources