Simple SELECT help - 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

Simple SELECT help

Author

Topic

port43
Starting Member

19 Posts

Posted - 2010-09-10 : 04:27:51

After looking at some of the other questions I'm ashamed to post something that is probably very simple but is giving me fits.
Anyway, I have two tables:

table1:


ProjectID	RoleID
2		OnCall
2		SME
2		TE
2		VP
9		TE
9		OnCall
4		VP
4		SME
4		DIR
4		TE
4		SME
6		TE
6		SME
6		OnCall

and
table2:


ProjectID	Desc
2		Project A
4		Project B
6		Project C
9		Project D

I need to select from table 1 any ProjectID that does NOT have a RoleID of OnCall and since there are multiple RoleIDs for each ProjectID I only want it listed in the output once.

Sample output:


ProjectID   Desc
    4       Project B

Thank you for your time and please don't post me in the "Twit List."

blessings,
Tony <><

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-09-10 : 04:45:54

This should do it:


SELECT
	*
FROM
	table2
WHERE 
	ProjectId NOT IN(
		SELECT ProjectId
		FROM table1
		WHERE RoleId='OnCall'
	)

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-09-10 : 04:47:07

This should also do it:


SELECT
	t2.*
FROM
	table2 t2
		LEFT JOIN
			table1 t1 ON
				t1.ProjectId=t2.ProjectId
				AND
				t1.RoleId='Oncall'
WHERE 
	t1.ProjectId IS NULL

port43
Starting Member

19 Posts

Posted - 2010-09-10 : 04:56:33

Sweet! Thank you so much. I knew I was overcomplicating it.

blessings,
Tony <><

port43
Starting Member

19 Posts

Posted - 2010-09-11 : 22:59:13

I've had a kink thrown at me - a third table:

table3:


RoleID	RoleVal
VP	100
DIR	75
SME	50
TE	25
OnCall	10
OA	5
HD	2

and now my output needs to be:


ProjectID   Desc         RoleID
    4       Project B      TE
    4       Project B      DIR

where I'm listing the ProjectID without a RoleID of OnCall AND I need my output to contain any RoleID I specify based on the weight of the RoleVal in table3.

I really do appreciate any help and I would also appreciate any reference material I could be directed to for creating queries. I'm sure I could get the hang of this if a had examples to look at.

blessings,
Tony <><

Ancy
Starting Member

23 Posts

Posted - 2010-09-13 : 02:59:38

Is this what u are looking for?

select t1.ProjectId, [Desc], RoleVAL from
(SELECT
*
FROM
table2
WHERE
ProjectId NOT IN(
SELECT ProjectId
FROM table1
WHERE RoleId='OnCall')
) as t4
join table1 t1 ON
T4.Projectid = t1.ProjectId
join table3 t3 ON
T1.Roleid = t3.RoleId

port43
Starting Member

19 Posts

Posted - 2010-09-15 : 23:48:28

Almost exept that I need the RoleID displayed and not RoleVal. I attempted to just change that in the first slect but that didn't do it. I've got "SQL Queries for Mere Mortals" coming in a day or two and hopefuly shortly after that I will understand what I'm doing.

quote:
Originally posted by Ancy

Is this what u are looking for?

select t1.ProjectId, [Desc], RoleVAL from
(SELECT
*
FROM
table2
WHERE
ProjectId NOT IN(
SELECT ProjectId
FROM table1
WHERE RoleId='OnCall')
) as t4
join table1 t1 ON
T4.Projectid = t1.ProjectId
join table3 t3 ON
T1.Roleid = t3.RoleId

blessings,
Tony <><

port43
Starting Member

19 Posts

Posted - 2010-09-16 : 00:34:19

Figured it out and I'm all set.

Thank you everyone for your help.

blessings,
Tony <><

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources