Select within a Select

[JohnnyLong]

Hi,

I am trying to fetch names from the rc_Candidates table and the corresponding FullPathName from the rc_DocumentAttachments table but I have to join using the rc_Documents table. This statement selects the 14 names I want but all the FullPathNames in the table

SELECT rc_Candidates.CandidateID, rc_Candidates.SurName, rc_Candidates.FirstName

FROM rc_Candidates

WHERE Surname LIKE 'test%' and FirstName Like '%'

(SELECT FullPathName FROM rc_DocumentAttachments INNER JOIN rc_Documents ON rc_DocumentAttachments.DocumentID = rc_Documents.DocumentID
WHERE rc_DocumentAttachments.CurrentCVDoc = 1)

So I managed to get this far:

SELECT rc_Candidates.CandidateID, rc_Candidates.SurName, rc_Candidates.FirstName
FROM rc_Candidates T1 INNER JOIN

(SELECT FullPathName FROM rc_DocumentAttachments T2 INNER JOIN rc_DocumentAttachments ON rc_Documents.DocumentID = T2.DocumentID
WHERE rc_DocumentAttachments.CurrentCVDoc = 1)

rc_Candidates ON rc_Documents.CandidateID = rc_Candidates.CandidateID

WHERE Surname LIKE 'test%' and FirstName Like '%'

This will parse but when I run it I'm getting
'The column prefix 'rc_Documents' does not match with a table name or alias name used in the query.'

Am I close or miles away??!

 

[vongrunt]

Close... you aliased derived table 'rc_Candidates' (again) instead of 'rc_Documents'.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba