SQL statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a SELECT statement within a SELECT statement as below (because of the
LEFT JOIN). I can't get the syntax correct, can anyone help? Thanks so Much!!

SELECT p.ClientID, (SELECT pa.* FROM ClientAddress pa INNER JOIN Address ad
ON pa.AddressID=ad.AddressID)pd
FROM Client AS p LEFT JOIN pd ON p.ClientID = pd.ClientID;
 
Doesn't this work:

SELECT p.ClientID FROM Client AS p LEFT JOIN
(SELECT pa.* FROM ClientAddress pa INNER JOIN
Address ad ON pa.AddressID=ad.AddressID) AS pd
ON p.ClientID = pd.ClientID;
 
smk23 said:
I need a SELECT statement within a SELECT statement as below (because of the
LEFT JOIN). I can't get the syntax correct, can anyone help? Thanks so Much!!

SELECT p.ClientID, (SELECT pa.* FROM ClientAddress pa INNER JOIN Address ad
ON pa.AddressID=ad.AddressID)pd
FROM Client AS p LEFT JOIN pd ON p.ClientID = pd.ClientID;
Click to expand...

Just a syntax error?

SELECT p.ClientID
,(SELECT pa.*
FROM ClientAddress pa
INNER JOIN
Address ad
ON pa.AddressID=ad.AddressID) pd
FROM Client AS p LEFT JOIN pd ON p.ClientID = pd.ClientID;


Try:

SELECT p.ClientID
,(SELECT pa.*
FROM ClientAddress AS pa
INNER JOIN
Address AS ad
ON pa.AddressID=ad.AddressID) AS pd
FROM Client AS p
LEFT JOIN
pd
ON p.ClientID = pd.ClientID;

The above will save.

1) You've named a table on the outer query "pd", and you've named the
sub-query on the SELECT clause "pd". I'm not sure whether that will
work. Please let me know.
 
Chris:
Thanks for the help.
Ken was right. I was trying to put the nested SELECT statement in the list
of columns, but it belonged in the FROM clause where you'd expect a reference
to a table to be.

I used to spend weeks and even months trying to figure these things out
before I discovered this forum. So many thanks to all the contributors.
 
Back
Top