Reputation: 505
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;
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;
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
Reputation: 12629
Just add these code to first query.
COUNT(pra.action_item_id) AS actions
.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_remarks
prr
.GROUP BY
with all columns
from SELECT
like GROUP BY pr.review_id, client_name, improvement_areas, strengths
.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