SQL Select Query Logic with 2 Parts

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

SQL Select Query Logic with 2 Parts

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-06-16 : 05:21:46

I have the following query which works fine

SELECT r.userId, r.programmeId, u.email, u.firstName, u.lastName, u.profileId, tblProgrammes.name, u.storeId, r.dateEnded AS lastPass, r.dateExpired
FROM tblUserRepeatHistory AS r INNER JOIN
tblUsers AS u ON r.userId = u.id INNER JOIN
tblProgrammes ON r.programmeId = tblProgrammes.id
WHERE (u.storeId = @storeId) AND (r.userId = @userId)
GROUP BY r.userId, r.programmeId, u.email, u.firstName, u.lastName, u.profileId, tblProgrammes.name, u.storeId, r.dateEnded, r.dateExpired
HAVING (DATEDIFF(D, MAX(r.dateExpired), GETDATE() + 31) >= 0)
ORDER BY tblProgrammes.name

Im basically checking an Historical table and looking if a user is due to repeat an exam again and this works fine. I basically get a record back as follows if the the above is true

10 25 staff-member jamie1 2 Deli Food Service 3 5 17/05/2010 10:14:21 15/07/2011 10:14:21

Now if I get a hit back I want to check another table and make sure that he hasnt currently doing this exam.

This query is as follows

SELECT TOP (1) r.id, p.maxSteps
FROM tblUserQuestionnaireHistory AS r INNER JOIN
tblProgrammes AS p ON r.programmeId = p.id AND r.stepId = p.maxSteps
WHERE (r.userId = @UserId) AND (r.programmeId = @ProgrammeId) AND (r.success = 1)
ORDER BY r.id DESC

So basically in the second query the parameters @ProgrammeId and @UserId should come from the first query. But since this is a query where I want the get the people who haven't done the test. I want the second query to be WHERE NOT EXISTS. I.e. If I get a hit back in the second query then the user is fine, but if I get nothing back then I want the joined query basically to return the same data as the first query

10 25 staff-member jamie1 2 Deli Food Service 3 5 17/05/2010 10:14:21 15/07/2011 10:14:21

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-06-16 : 06:34:57

Maybe to make this query a bit simpler lets say I have

SELECT r.userId, r.programmeId, u.email, u.firstName
FROM tblUserRepeatHistory

For Each Record Returned I would then would want to join that with

SELECT TOP (1) r.id
FROM tblUserQuestionnaireHistory AS r
WHERE (r.userId = @UserId) AND (r.programmeId = @ProgrammeId) AND (r.success = 1) AND r.stepId = 9

How could I join the two queries as such

SELECT r.userId, r.programmeId, u.email, u.firstName
FROM tblUserRepeatHistory (SELECT WHERE NOT EXISTS TOP (1) r.id
FROM tblUserQuestionnaireHistory AS r
WHERE (r.userId = @UserId) AND (r.programmeId = @ProgrammeId) AND (r.success = 1) AND r.stepId = 9)

Where @ProgrammeId and @userId would come from the first select

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources