another noob query - 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 2008 Forums

Transact-SQL (2008)

another noob query

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-02-15 : 12:06:08

I have 2 tables

One table holds all the lessons one could follow.
Second one holds all the received point one user had on a lesson

Note: one user doesnt follow all lessons. So there are gaps for sure

I want my query to give for each user all the lessons with its points, even if that user havent followed that lesson

here is the table layout.

first table:
id lessonid description

second table
id userid lessonid points

i tought this was just a select from tablelesson and then do
a left outer join to the tablepoints but this doesnt seem to be that easy :(

thx in advance

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-15 : 12:56:53

come up with a couple of records for both tables and how your desired output should look like as per given information. this will help us to transform your requirements

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-15 : 13:03:59

You will need to include, or derive, a User table so your FROM clause looks something like:


FROM Users U
	CROSS JOIN Lessions L
	LEFT JOIN UserLessions X
		ON U.userid = X.userid
			AND L.kessionid = X.lessionid

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-02-16 : 03:28:57

thx alot both of you.... ifor cracked it already

:o

the qry gives me 2300 records and i have 100 users and 23 lessons, so that seems dead on !
now i have to understand the qry too :p ( optional :p )

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-02-16 : 03:40:54

on second tought... its just not quite what i need...

the result is good but i only want the users' lessonpoints who have had at least one point on one lesson

so i have to do something with userlessons and users..

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2011-02-16 : 03:45:51

i think i got it like this
excuse me for the real tablenames

FROM tblusers U
CROSS JOIN vwkoppelingopleidingen KOP
LEFT JOIN
(SELECT *
FROM tblevaluatieantwoorden
WHERE disciplineid is not null) P
ON U.id = P.userid
AND KOP.disciplineid = P.disciplineid

WHERE U.id IN ( SELECT userid
FROM tblevaluatieantwoorden
WHERE disciplineid is not null)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources