Left Join Table Var - Missing Something? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Left Join Table Var - Missing Something?

Author

Topic

jgombala
Starting Member

4 Posts

Posted - 2006-04-03 : 15:12:38

I have a table of possible quizzes to be taken and a populated table variable of the quizzes taken by particpant. However when I try to join the table var with the quizzes table the LEFT join dosen't take affect. See sample queries below.... notice in the first example that works the where clause for userid is in the join statement, however moving it outside seems to make the LEFT JOIN not work as in the last example.

I am trying to show all quizzes for each participant in the table @results, even if they have taken it or not

---------- THIS ONE WORKS --------------
SELECT s.*
FROM SURVEY s
LEFT JOIN (SELECT * FROM @results WHERE userid = '055892') r ON r.survey_id = s.survey_id
WHERE s.survey_type_id = 2

---------- THIS DOESNT WORK ------------
SELECT s.*
FROM SURVEY s
LEFT JOIN @results r ON r.survey_id = s.survey_id
WHERE s.survey_type_id = 2
AND r.userid = '055892'

TimS
Posting Yak Master

198 Posts

Posted - 2006-04-03 : 15:20:36

SELECT s.*
FROM SURVEY s
LEFT JOIN @results r ON r.survey_id = s.survey_id AND r.userid = '055892'
WHERE s.survey_type_id = 2

If nothing if in the left table then r.userid is null; you said not to show nulls with r.userid = '055892'

Tim S

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-03 : 15:23:43

Move "AND r.userid = '055892'" to the JOIN criteria to only return rows from r where user id = '055892' but all rows from s.

Be One with the Optimizer
TG

jgombala
Starting Member

4 Posts

Posted - 2006-04-03 : 16:08:39

Makes sense if I am trying to select for just one user, but if I remove the AND r.userid = '055892' it again ignores the LEFT JOIN...

SELECT *
FROM SURVEY s
LEFT JOIN @results r ON r.survey_id = s.survey_id
WHERE s.survey_type_id = 2

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-03 : 16:20:00

I'm not seeing a problem with the LEFT JOIN. What are your results and what are your expected results:


set nocount on
declare @survey table (survey_id int, survey_type_id int)
declare @results table (survey_id int)

insert @survey 
select 1, 2 union all
select 2, 2 union all
select 3, 2 union all
select 4, 2

insert @results
select 1 union all
select 2

SELECT *
FROM @SURVEY s
LEFT JOIN @results r ON r.survey_id = s.survey_id
WHERE s.survey_type_id = 2

output:
survey_id   survey_type_id survey_id   
----------- -------------- ----------- 
1           2              1
2           2              2
3           2              NULL
4           2              NULL

Be One with the Optimizer
TG

jgombala
Starting Member

4 Posts

Posted - 2006-04-03 : 16:33:04

My result set is 25K rows... but a sample shows 2 returned records when I expected to get 10 (2 that the participant took and 8 that they didn't take).

The first userid "Jase57" has only taken 2 quizzes, received 15 points for each. (i would expect the LEFT join to show the other 8 quizzes) The second user "01Nadeau" has taken all 10 quizzess and recieved points.

******** QUERY ***********
SELECT TOP 15 s.survey_id, s.survey_name, r.userid, r.nPointValue
FROM SURVEY s
LEFT JOIN @results r ON r.survey_id = s.survey_id
WHERE s.survey_type_id = 2
order by userid

59 QTC Weekly Quiz #1 Jase57 15
60 QTC Weekly Quiz #2 Jase57 15
59 QTC Weekly Quiz #1 01Nadeau 15
60 QTC Weekly Quiz #2 01Nadeau 15
61 QTC Weekly Quiz #3 01Nadeau 15
62 QTC Weekly Quiz #4 01Nadeau 15
65 QTC Weekly Quiz #5 01Nadeau 15
66 QTC Weekly Quiz #6 01Nadeau 15
69 QTC Weekly Quiz #7 01Nadeau 15
68 QTC Weekly Quiz #8 01Nadeau 15
70 QTC Weekly Quiz #9 01Nadeau 1
71 QTC Weekly Quiz #10 01Nadeau 1

********* @Results TABLE ****************
DECLARE @results TABLE (
userid VARCHAR(30)
,date_created DATETIME
,tParticipantName VARCHAR(500)
,point_id INT
,tActivityName VARCHAR(500)
,training_month VARCHAR(20)
,nPointValue INT
,dPointEntered DATETIME
,pointtype_id INT
,survey_grouping_id UNIQUEIDENTIFIER
,survey_id INT
,survey_name VARCHAR(500)
,survey_answered_correctly INT
,survey_enddate DATETIME
,training_endDate DATETIME
,dPointTaken DATETIME
)

***** SURVEY TABLE *****
CREATE TABLE [dbo].[SURVEY](
[SURVEY_ID] [int] IDENTITY(1,1) NOT NULL,
[SURVEY_NAME] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SURVEY_DESCRIPTION] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[START_DATE] [datetime] NULL CONSTRAINT [DF__SURVEY__START_DA__7B264821] DEFAULT (getdate()),
[END_DATE] [datetime] NULL CONSTRAINT [DF__SURVEY__END_DATE__7C1A6C5A] DEFAULT (getdate()),
[SURVEY_TYPE_ID] [int] NOT NULL,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-03 : 17:46:52

What is the survey_type_id for the Jase57 rows in SURVEY that didn't get returned? If it is not "2" then that is why.

You provided the code to create the tables...that's good, now post the code to insert the sample rows into your tables that will illustrate the problem (similar to what I provided above) so we can see the problem "in action"

Be One with the Optimizer
TG

jgombala
Starting Member

4 Posts

Posted - 2006-04-03 : 17:56:14

TG, Thank you for your help... you actually sparked the idea in my head with what was wrong. What I did was create a CROSS JOIN between the user table and the survey table and then use that for my join. below you can find my end result that I was working towards. Might be better ways of doing this, but in the long run this comes down to a poor database design that I inherited. This code is going to get slower and slower as more quizzes and users are added, but I am really not sure of another solution.

/*
UPDATE WITH QUIZZES NOT TAKEN
*/
INSERT INTO @results (userid, date_created, tParticipantName, point_id, tActivityName, nPointValue, pointtype_id, survey_id, survey_name, survey_startdate, survey_enddate)
SELECT u.userid, u.date_created, u.first_name+' '+u.last_name, -999, survey_name, 0, 2, survey_id, survey_name, start_date, end_date
FROM SURVEY s, USERS u
WHERE u.userID IS NOT NULL
AND survey_type_id = 2
AND survey_id NOT IN (SELECT survey_id FROM @results WHERE userid = u.userid)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources