Query Help

What I want to generate is a list of all users and if they belong to a
site or not. Below is what I have for tables, sample data and the
results. I am a newb with writing my own SQL and could use a few
pointers and help.

For example if I have the following data:

Table: [Users]
UserIDUserName
1Bob
2Joe
3Sam

Table: [Users_Sites]
UserIDSiteID
12
32

Table: [Sites]
SiteIDSiteName
1Flower
2Spring
3Bush

If I pick Site 1 I get the list:

UserIDSiteID
<null>1
<null>1
<null>1

If I pick Site 2 I get the list:

UserIDSiteID
12
<null>2
32

Thanks in advance...Will (wrhighfield@.hotmail.com) writes:
> What I want to generate is a list of all users and if they belong to a
> site or not. Below is what I have for tables, sample data and the
> results. I am a newb with writing my own SQL and could use a few
> pointers and help.

SELECT us.SiteID, u.UserID
FROM Users_Sites us
LEFT JOIN Users u ON us.UserID = u.UserID
WHERE us.SiteID = @.siteid

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Query Assistance

Hi,
Assuming the following table structure and sample data:
CREATE TABLE TestTab
(Col1 varchar(30) NOT NULL,
Col2 varchar(30) NOT NULL,
Col3 varchar(50) NOT NULL)
Col1 Col2 Col3
XYZ 54 Test
XYZ 54 Simple
ABC 28 Bogus
How can I return the only one entry for a combination of Col1 and Col2?
I.e., Resultset
Col1 Col2 Col3
XYZ 54 Test
ABC 28 Bogus
It doesn't matter wich record is return i.e., Col3 Test or Simple is fine.
Thanks
JerrySELECT col1, col2, MIN(col3)
FROM Test
GROUP BY col1, col2;|||>> How can I return the only one entry for a combination of Col1 and Col2?
You have to specify which row you want to return
It may not matter to the users, but it matters to the DBMS to consistently
derive the resultset. One option would be to simply use an extrema function
on col3 like:
SELECT col1, col2, MAX( col3 )
FROM tbl
GROUP BY col1, col2 ;
Anith|||Duh! Spaced that one. Thanks guys!
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126563751.937497.46640@.g43g2000cwa.googlegroups.com...
> SELECT col1, col2, MIN(col3)
> FROM Test
> GROUP BY col1, col2;
>