Link to home
Start Free TrialLog in
Avatar of Ingo Foerster
Ingo Foerster

asked on

Second left Join with count

Add a third table to a query that works well.

I have the following query:
"SELECT * FROM (Select test_case.id,test_case.description,test_case.folder,test_case.pubstate,test_case.owner,test_case.last_state_mrdev,test_case.usebxf22 FROM test_case )t1 LEFT JOIN (SELECT * FROM case_additional)t2  ON t1.id = t2.case_id "

Open in new window


This brings the right result. Now I want to add the single query
SELECT COUNT(*) AS total FROM test_comments WHERE test_comments.case_id=t1.id

Open in new window

To the query above to get the "total" as additional filed in the query result. How I n do this? Two left join? I expected

"SELECT * FROM (Select test_case.id,test_case.description,test_case.folder,test_case.pubstate,test_case.owner,test_case.last_state_mrdev,test_case.usebxf22 FROM test_case )t1 LEFT JOIN (SELECT * FROM case_additional)t2  ON t1.id = t2.case_id  LEFT JOIN SELECT t3.*,COUNT(*) AS total FROM test_comments ON t1.id = t3.case_id"

Open in new window


But this will end in a error. Any hint?
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ingo Foerster
Ingo Foerster

ASKER

Many thanks for this rapid help. Now I understand what I did wrong.