nested subquery brackets - 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

nested subquery brackets

Author

Topic

iradev
Starting Member

45 Posts

Posted - 2010-07-09 : 08:59:19

Can anyone fix the above code so the brackets are correct? I cant seem to get it working :/

SELECT DISTINCT
dbo.Jobs.JobId,
dbo.Jobs.JobRefNo,
dbo.Jobs.JobTitle AS [Role Title],
dbo.Jobs.CreatedOn AS [Role Reg.]

FROM dbo.Jobs INNER JOIN dbo.ApplicantActions ON dbo.Jobs.JobId = dbo.ApplicantActions.JobId
INNER JOIN dbo.NotebookLinks ON dbo.Jobs.JobId = dbo.NotebookLinks.JobId
INNER JOIN dbo.NotebookItems ON dbo.NotebookLinks.NotebookItemId = dbo.NotebookItems.NotebookItemId

WHERE NOT EXISTS
( SELECT dbo.ApplicantActions.JobId
FROM dbo.ApplicantActions
WHERE dbo.Jobs.JobId = dbo.ApplicantActions.JobId AND dbo.ApplicantActions.StatusId=44
(SELECT dbo.NotebookLinks.NotebookItemId
FROM dbo.NotebookLinks
WHERE dbo.NotebookItems.NotebookItemId = dbo.NotebookLinks.NotebookItemId AND dbo.NotebookLinks.NotebookTypeFolder=55));

AND (dbo.Jobs.CreatedOn BETWEEN DATEADD(ww, -1, GETDATE()) AND DATEADD(ww, -0, GETDATE()))

ORDER BY dbo.Jobs.JobId ASC

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 09:07:03

maybe you meant:
...
where
NOT EXISTS (SELECT dbo.ApplicantActions.JobId FROM dbo.ApplicantActions ...)
AND
NOT EXISTS (SELECT dbo.NotebookLinks.NotebookItemId FROM dbo.NotebookLinks ...)
AND
(dbo.Jobs.CreatedOn BETWEEN DATEADD(ww, -1, GETDATE()) AND DATEADD(ww, -0, GETDATE()))

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-09 : 09:09:31

[code]SELECT j.JobId,
j.JobRefNo,
j.JobTitle AS [Role Title],
j.CreatedOn AS [Role Reg.],
CASE nl.NotebookTypeFolder
WHEN 55 THEN nl.NotebookItemId
ELSE NULL
END AS NotebookItemId
FROM dbo.Jobs AS j
INNER JOIN dbo.ApplicantActions AS aa ON aa.JobId = j.JobId
INNER JOIN dbo.NotebookLinks AS nl ON nl.JobId = aa.JobId
INNER JOIN dbo.NotebookItems AS ni ON ni.NotebookItemId = nl.NotebookItemId
WHERE NOT EXISTS (
SELECT *
FROM dbo.ApplicantActions AS x
WHERE x.JobId = j.JobId
AND x.StatusId = 44
)
AND j.CreatedOn >= DATEDIFF(DAY, 8, GETDATE()
AND j.CreatedOn < DATEDIFF(DAY, 0, GETDATE()
ORDER BY j.JobId[/code]

N 56°04'39.26"
E 12°55'05.63"

iradev
Starting Member

45 Posts

Posted - 2010-07-09 : 09:18:42

Peso: I got the following error:
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'AND'.
Im using MS SQL 2005

webfred: Yes, that works, thank you once again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-09 : 09:26:06

Add an extra ")" to the end of each DATEDIFF filter clause.

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 09:29:14

quote:
Originally posted by iradev

Peso: I got the following error:
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'AND'.
Im using MS SQL 2005

webfred: Yes, that works, thank you once again

welcome

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

iradev
Starting Member

45 Posts

Posted - 2010-07-09 : 09:39:22

quote:
Originally posted by Peso

Add an extra ")" to the end of each DATEDIFF filter clause.

N 56°04'39.26"
E 12°55'05.63"

Thanks, that worked as well!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources