Confused by joins... - 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

Confused by joins...

Author

Topic

arenigltd
Starting Member

4 Posts

Posted - 2010-07-12 : 08:22:59

I have simplified my example tables here to try to demonstrate my problem...

I have a list of persons, some of whom are instructors.
I have a list of resources, on which some or all of the
instructors can teach.
So, if I select resource #1 for instance, I want to see a Y or N against the instructors. My tables are as follows:

members table
membCode name isInstructor
----------------------------------------------------
1 Bob N
2 Jim Y
3 Dave Y
4 Alice N
5 Tom N
6 Wendy Y
7 Eddie Y

resourcelink table
resource membCode
--------------------------------
1 2
1 7
2 3
3 3
3 6
3 7

Desired output if resource #1 selected :

Jim Y
Dave N
Wendy N
Eddie Y

I've tried RIGHT OUTER, LEFT INNERS, FULL OUTERS, UPSIDE DOWN INSIDE OUT JOINs and can't seem to make it work.
Any help gratefully received.
Thank you for looking
Regards
Martin

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2010-07-12 : 08:31:24

I didn't understand logic about selection Dave and weddie.

Resource#1 membcodes are (2 and 7),
so, if we check in first table, we can get Jim and eddie..

But what about Dave and weddie?? y u included them in result set??

why not Bob or Alice etc?

arenigltd
Starting Member

4 Posts

Posted - 2010-07-12 : 08:40:35

My Apologies, I typed the wrong name.!!! I've edited the examples now and removed group field as this would only confuse anyway.

I have 7 members, 4 of whom are instructors (Jim, Dave, Wendy & Eddie)
Against Resource #1 are allocated instructors 2 & 7 (Jim and Eddie)

I want my user to be able to see all the instructors potentially available for each resource, so when the select resource #1 they are presented with an opportunity to select/de-select an instructor.

So, in the above data examples, resource #2 would show only Dave with Y, the others with 'N'

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-12 : 08:59:25

select distinct
m.name,
case when r.membCode is null then 'N' else 'Y' end as Instructor
from members m
left join resourcelink r on r.membCode = m.membCode
where m.isInstructor = 'Y'

No, you're never too old to Yak'n'Roll if you're too young to die.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-07-12 : 09:17:48

You probably need to understand the order in which SQL clauses are evaluated. Roughly:
FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP

As FROM is evaluated before WHERE, any filters on outer joined tables need to be applied on the JOIN and not in the WHERE clause.
(If the filter is applied in the WHERE clause, any NULLs would be removed and the effect the same as an INNER JOIN.)


-- *** Test Data ***
DECLARE @members TABLE
(
	membCode int NOT NULL
	,[name] varchar(20) NOT NULL
	,isInstructor char(1) NOT NULL
)
INSERT INTO @members
SELECT 1, 'Bob', 'N'
UNION ALL SELECT 2, 'Jim', 'Y'
UNION ALL SELECT 3, 'Dave', 'Y'
UNION ALL SELECT 4, 'Alice', 'N'
UNION ALL SELECT 5, 'Tom', 'N'
UNION ALL SELECT 6, 'Wendy', 'Y'
UNION ALL SELECT 7, 'Eddie', 'Y'

DECLARE @resourcelink TABLE
(
	resource int NOT NULL
	,membCode int NOT NULL
)
INSERT INTO @resourcelink
SELECT 1, 2
UNION ALL SELECT 1, 7
UNION ALL SELECT 2, 3
UNION ALL SELECT 3, 3
UNION ALL SELECT 3, 6
UNION ALL SELECT 3, 7
-- *** End Test Data ***

DECLARE @resource int
SET @resource = 1

SELECT M.[name]
	,CASE WHEN R.membCode IS NULL THEN 'N' ELSE 'Y' END AS Instructor
FROM @members M
	LEFT JOIN @resourcelink R
		ON M.membCode = R.membCode
			-- resource filter must be applied here
			AND R.resource = @resource
WHERE M.isInstructor = 'Y'
-- try this an see what happens
--	AND R.resource = @resource

arenigltd
Starting Member

4 Posts

Posted - 2010-07-12 : 09:26:19

Dear webfred,

When I translate your example to fit my actual tables, it still seems only to return the instructors allocated to the resource but not the other instructors.
If it helps, here are my real tables...

members table


group   membCode membName               isInstructor
3	12	Guest User	        False
3	18	Robert Flowerdew	False
3	19	James Technophobe	True
3	20	Brian Balderdash	True
3	21	Edwin Starr	        True

resourceLink table


groupCode resourceCode membCode
3	   1	       19
3	   1	       21
3	   2	       19
3	   3	       19
3	   3	       20
3	   3	       21
3	   4	       20
3	   4	       21
3	   5	       19
3	   5	       21

So, I want my user to be able to select each resource from a grid of resources and be presented with a list of ALL instructors for that group, and be able to check/uncheck and subsequently update resourceLink table. If resource #1 selected:


19	James Technophobe	True
20	Brian Balderdash	False
21	Edwin Starr             True

MemberCode #20 is not in resourceLink against resource#1, but needs to appear so my user can select him for this resource if they so wish.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-12 : 10:30:35

Please see Ifor's post.
He has taken my solution and has it made more clear with testdata.
He also is considering the wanted condition on which resource should be taken what I had forgotten.

No, you're never too old to Yak'n'Roll if you're too young to die.

arenigltd
Starting Member

4 Posts

Posted - 2010-07-12 : 10:46:10

WONDERFUL !!!
Thank you Ifor and webfred. That does indeed work.
And I see I have a lot still to learn.

Thank goodness for kind people willing to help a beginner.
Cheers

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-12 : 10:49:23

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources