Using subquery in select statement

[JJames100]

Also, the client_id = part of the where clause worked whether I put it at the end or included it in the second select instead.

 

[Lobo360]

Also got the following to work which contains a where clause (if I remove the part of the stmt starting with 'AND' at the end. Evidently, the 'is null' is causing it to have an error)

SELECT qryClientFromSC.client_id, Pub.pt_program.program_id... ...

Hi JJames100. How interesting to see another post to this thread! I'm still not certain if the issue I experienced was the same as that discussed by those that started this thread and so also unsure if it's the same as the problems that led you to this thread also, however I couldn't but help take a look at the SQL you posted.
Sorry if I don't understand the issue, and ofcourse don't understand your data but it does look different to the issue that I experienced in that with the Inner joins you have in use I doubt that what you were trying to acheive was a Correllated Sub Query. And so in which case, I'm not sure if your script needs the further sub queries in the From section. May I suggest that your script may read and run better if written like this:


SELECT
Pub.client.client_id
, Pub.pt_program.program_id
, Pub.pt_program.program_seq
, Pub.pt_program.admit_date
, Pub.pt_program.discharge_date
, Pub.address.address_reference_id
, Pub.client.name_title
, Pub.client.last_name
, Pub.client.first_name
, Pub.client.middle
, Pub.client.DOB
, Pub.client.gender
, Pub.address.address_type
, Pub.address.Line1
, Pub.address.Line2
, Pub.address.City
, Pub.address.Zip
, Pub.address.County_id
, Pub.address.end_date
, Pub.address.primary_address
FROM
Pub.client
INNER JOIN Pub.pt_program ON pub.client.client_id = Pub.pt_program.client_id
INNER JOIN Pub.address ON Pub.pt_program.client_id = Pub.address.address_reference_id
WHERE
Pub.client.client_id=*****
AND Pub.address.address_type='Client'
AND Pub.address.end_date Is Null



or even this:
SELECT
Pub.client.client_id
, Pub.pt_program.program_id
, Pub.pt_program.program_seq
, Pub.pt_program.admit_date
, Pub.pt_program.discharge_date
, Pub.address.address_reference_id
, Pub.client.name_title
, Pub.client.last_name
, Pub.client.first_name
, Pub.client.middle
, Pub.client.DOB
, Pub.client.gender
, Pub.address.address_type
, Pub.address.Line1
, Pub.address.Line2
, Pub.address.City
, Pub.address.Zip
, Pub.address.County_id
, Pub.address.end_date
, Pub.address.primary_address
FROM
Pub.client
INNER JOIN Pub.pt_program ON pub.client.client_id = Pub.pt_program.client_id
INNER JOIN Pub.address ON Pub.pt_program.client_id = Pub.address.address_reference_id AND Pub.address.address_type='Client' AND Pub.address.end_date Is Null

WHERE
Pub.client.client_id=*****


You could even join the Pub.address table to Pub.client
instead of Pub.pt_program.