CODEWITHSUNDEEP
sql-serverleft-join
prudvi raju
prudvi raju

Reputation: 505

Combine 2 queries with LEFT JOIN and 3 tables

First Left JOIN with 2 tables (project_reviews & project_review_remarks)

SELECT pr.review_id,client_name,improvement_areas, strengths
FROM project_reviews pr
LEFT JOIN project_review_remarks pra ON pr.review_id = pra.review_id
WHERE review_status IN ('COMPLETED') 
ORDER BY review_date DESC;

enter image description here

Second Left JOIN with 2 tables (project_reviews & project_review_remarks)

SELECT pr.review_id, COUNT(pra.action_item_id) AS actions
FROM project_reviews pr
LEFT JOIN project_review_action_item pra ON pr.review_id = pra.review_id
GROUP BY pr.review_id;

enter image description here

In both the queries, project_reviews is a common table,

IN first Query is ONE-ONE relationship IN second Query is ONE-MANY relationship (WHERE I COUNTED it many rows using group by)

I want to merge both queries into one query, because project_reviews is a common table with review ID and show the count of action_item into one table.

Upvotes: 0

Views: 202

Answers (1)

Karan
Karan

Reputation: 12629

Just add these code to first query.

  • In select add COUNT(pra.action_item_id) AS actions.
  • Add LEFT JOIN project_review_action_item pra ON pr.review_id = pra.review_id. Make sure that your both LEFT JOIN tables has different alias as I have updated project_review_remarksprr.
  • Add GROUP BY with all columns from SELECT like GROUP BY pr.review_id, client_name, improvement_areas, strengths.
  • To GROUP BY with TEXT columns you can cast it to VARCHAR(MAX) or NVARCHAR(MAX) whichever you think appropriate.

Check complete query below.

SELECT  pr.review_id, 
        client_name, 
        CAST(improvement_areas AS VARCHAR(MAX)) AS improvement_areas, 
        CAST(strengths AS VARCHAR(MAX)) AS strengths, 
        COUNT(pra.action_item_id) AS actions
FROM project_reviews pr
LEFT JOIN project_review_remarks prr
    ON pr.review_id = prr.review_id
LEFT JOIN project_review_action_item pra
    ON pr.review_id = pra.review_id
WHERE pr.review_status IN ('COMPLETED')
GROUP BY pr.review_id, 
         client_name, 
         CAST(improvement_areas AS VARCHAR(MAX)), 
         CAST(strengths AS VARCHAR(MAX))
ORDER BY review_date DESC;

Upvotes: 1

Related Questions