Query Question - 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 2000 Forums

Transact-SQL (2000)

Query Question

Author

Topic

doublek321
Starting Member

6 Posts

Posted - 2005-07-15 : 23:47:03

I have 2 tables:

TABLE: Member
PK: id
MemberName

TABLE: Portrait
PK: id
FK: memberId
picURL
PicIsUsedInProfile

The business rules are that a user can have between 0 to 3 pictures uploaded. However, one and only one of these pics can be used as the "profile pic". If the pic is used as the profile pic then the column is set to 1. If it's not used as the profile pic then it's either set to null or 0. My goal is to write a query that returns all member's (regardless of whether they have a pic or not) memberName as well as the picURL if they have a "profile pic" set (in other words column picIsUsedInProfile is set to 1). I'm not sure if this can be done in 1 query or a group of several queries. 1 query would be ideal but I'd be okay if it had to be written in several. I tried breaking it into several below and they both are okay. However, when I can't seem to return members who have 1 or more entries in the portraits table where NONE of these have a "profile pic" set. I know I can test for "isProfilePortrait = 0" but that's not good enough because I want to return a max of 1 row per member. In my scenario, however, a member can theoretically have 2 or 3 pics uploaded with none of them set to their "profile pic". I hope this makes sense. Any help would be GREATLY appreciated.

-- "members with profile portraits"
select m.*, p.*
from member m
inner join portrait p
on m.id = p.memberId
where p.isprofileportrait = 1

-- "members with no portaits"
select m.*
from member m
left outer join portrait p
on m.id = p.memberId
where p.id is null

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-16 : 01:13:31

Can you post some sample data and expected result?

Madhivanan

Failing to plan is Planning to fail

doublek321
Starting Member

6 Posts

Posted - 2005-07-16 : 08:30:06

Sure. The Member table is...

ID MemberName
1 Alan
2 Bob
3 Charlie
4 David
5 Ed
6 Fred
7 Gary
8 Henry
9 Irv
10 Jack
11 Kevin
12 Lenny
13 Morton
14 Nick
15 Orvis

The portrait table is...
ID MemberID PicURL IsProfilePortrait
1 1 www.a.com 0
2 1 www.b.com 0
3 1 www.c.com 1
4 2 www.d.com 0
5 2 www.e.com 0
6 2 www.f.com 0
7 3 www.g.com 0
8 3 www.h.com 0
9 4 www.i.com 0
10 5 www.j.com 1
11 6 www.k.com 0
12 6 www.l.com 1

I'm looking for this output (ideally in 1 query but if it had to be a union of several that would be ok too)...

ID MemName PicURL
1 Alan www.c.com
2 Bob NULL
3 Charlie NULL
4 David NULL
5 Ed www.j.com
6 Fred www.l.com
7 Gary NULL
8 Henry NULL
9 Irv NULL
10 Jack NULL
11 Kevin NULL
12 Lenny NULL
13 Morton NULL
14 Nick NULL
15 Orvis NULL

mowafy
Starting Member

1 Post

Posted - 2005-07-16 : 10:40:43

i think you can use an left outer join statment
that select * records from member an portrait

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-16 : 10:41:24

"Query Question" -- isn't that redundant?

- Jeff

doublek321
Starting Member

6 Posts

Posted - 2005-07-16 : 12:18:56

An outer join would work but it would return duplicates. Below are 2 examples of outer joins. Both return dupes. I just want each memberName to show up once and only once.

------------------------------------------------
OUTER JOIN EXAMPLE 1:

select m.id, m.memberName, p.picURL, p.IsProfilePortrait
from member m
left outer join portrait p
on m.id = p.memberId

1 Alan www.a.com 0
1 Alan www.b.com 0
1 Alan www.c.com 1
2 Bob www.d.com 0
2 Bob www.e.com 0
2 Bob www.f.com 0
3 Charlie www.g.com 0
3 Charlie www.h.com 0
4 David www.i.com 0
5 Ed www.j.com 1
6 Fred www.k.com 0
6 Fred www.l.com 1
7 Gary NULL NULL
8 Henry NULL NULL
9 Irv NULL NULL
10 Jack NULL NULL
11 Kevin NULL NULL
12 Lenny NULL NULL
13 Morton NULL NULL
14 Nick NULL NULL
15 Orvis NULL NULL
------------------------------------------------
------------------------------------------------
OUTER JOIN EXAMPLE 2:

select m.id, m.memberName, p.picURL, p.IsProfilePortrait
from member m
left outer join portrait p
on m.id = p.memberId
where p.isProfilePortrait = 0

1 Alan www.a.com 0
1 Alan www.b.com 0
2 Bob www.d.com 0
2 Bob www.e.com 0
2 Bob www.f.com 0
3 Charlie www.g.com 0
3 Charlie www.h.com 0
4 David www.i.com 0
6 Fred www.k.com 0
-------------------------------------------------

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-16 : 13:44:39

quote:
Originally posted by doublek321

My goal is to write a query that returns all member's (regardless of whether they have a pic or not) memberName as well as the picURL if they have a "profile pic" set (in other words column picIsUsedInProfile is set to 1).

SELECT M.MemberName, P.picURL

  FROM Member M
  LEFT OUTER JOIN Portrait P 
    ON P.memberId = M.id
    AND P.PicIsUsedInProfile = 1 -- Only join "profile pic" rows

LEFT JOIN on PicIsUsedInProfile will produce one row per member (as long as PicIsUsedInProfile is only set to 1 once for each member. Members with no picture or no profile pic will return column picURL set to NULL.

doublek321
Starting Member

6 Posts

Posted - 2005-07-16 : 15:54:04

Sam C, that doesn't cover the case though where a user has a record (or multiple records) in the Portrait table where *none of them* have column PicIsUsedInProfile=1. The Member with id 2 is such a case. He has 3 entries in the Portrait table but none of them have PicIsUsedInProfile=1. So this Member would not return as part of the query results. And I need *all* members to return (once and only once).

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-16 : 17:38:56

doublek -- did you try Sam's query?

- Jeff

doublek321
Starting Member

6 Posts

Posted - 2005-07-16 : 17:42:24

Whoops, it totally does work. SamC, thanks a bunch for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources