November 18, 2009 at 1:04 pm
I have 3 tables, tblUser, tblTest and a join table..tblUserTest (just TestID and UserID in this table)
I'm not sure the best way to get the data I'm looking for, but what I was thinking is I want to pass in UserID and what I want returned is TestID, TestName and UserID...I want a list of all tests and the UserID or NULL depending if that UserID/TestID have a match in the join table....does this make sense?
Please help!
November 18, 2009 at 1:14 pm
Post the tables structures and relationships between the tables.
November 18, 2009 at 1:19 pm
tblUser
UserID
Name
...
other basic user info
...
ynDelete
tblUserTests
UserTestID
UserID
TestID
tblTest
TestID
TestName
ynDelete
also, I've gotten this far with my query...I don't think it's right, but I'm getting closer
SELECT T.TestID,
T.TestName,
CASE WHEN UT.UserID = @user-id THEN 1 ELSE 0 END AS HasTest
FROM tblTest T
LEFT JOIN tblUserTests UT ON T.TestID = UT.TestID
LEFT JOIN tblUser U ON U.UserID = UT.UserID
WHERE T.ynDelete = 0
AND (UT.UserID = @user-id OR UT.UserID IS NULL)
November 18, 2009 at 1:57 pm
I am starting to understand a little bit more now. But encourage you to post the create table scripts and some insert scripts to test the data. It would remove a lot of confusion from my end.
And finally describe how you want the data output to be.
Read this article before replying.
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 18, 2009 at 3:20 pm
Right there with Bru on this one.
I think I have a grasp of what you want but could be jumping at conclusions that may be wrong without more information.
However, based on the query you provided - it looks like you are headed in the right direction.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply