CODEWITHSUNDEEP
sqlms-access-97
robpws
robpws

Reputation: 79

Access97 LEFT JOIN SELECT

I'm stuck with some access97 sql,

SELECT Master.Text, Sub.Cnt
FROM Master
INNER JOIN [
SELECT Number, COUNT(*) As Cnt FROM SubTable GROUP BY Number]. AS Sub
ON Master.Number=Sub.Number
WHERE Master.Number=1;

Behaves as I would expect however Where I need to square bracket the 'SubTable' Name I recieve an error in FROM clause highlighting GROUP

SELECT Master.Text, Sub.Cnt
FROM Master
INNER JOIN [
SELECT Number, COUNT(*) As Cnt FROM [Sub Table] GROUP BY Number]. AS Sub
ON Master.Number=Sub.Number
WHERE Master.Number=1;

Can anyone point me in the right direction?

Thanks

Upvotes: 0

Views: 303

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Square brackets look really strange in the query. Try using regular parentheses for the subquery:

SELECT Master.Text, Sub.Cnt
FROM Master INNER JOIN 
     (SELECT Number, COUNT(*) As Cnt
      FROM [Sub Table]
      GROUP BY Number
     ) AS Sub
     ON Master.Number = Sub.Number
WHERE Master.Number = 1;

EDIT:

You can rewrite this query as:

SELECT Master.Text, COUNT(*) as Cnt
FROM Master INNER JOIN 
     [Sub Table] as sub
     ON Master.Number = Sub.Number
WHERE Master.Number = 1
GROUP BY Number;

Upvotes: 0

Related Questions