Left join is equal to inner join?

[Guest]

Hi All

I try to calculate the result by using left join but it still returns the
output same as using inner join.

************************************
Table1(T1)
Status_id Score_id SumOfAmount
Chief Excellent 1
Chief Fair 1
Chief Good 2
DD Fair 3
DD Good 4
DD Poor 1
DMG Fail 1
DMG Fair 5
DMG Good 4
DMG Poor 2
MGR Fail 5
MGR Fair 7
MGR Good 2
MGR Poor 2
Supervisor Excellent 5
Supervisor Fair 2
Supervisor Good 5

Table2(T2)
SCORE_id
Excellent
Fail
Fair
Good
Poor

SQL statement
SELECT T1.Status_id, T2.SCORE_id, T1.SumOfAmount
FROM T2
LEFT JOIN T1
ON T2.SCORE_id = T1.Score_id
ORDER BY T1.Status_id, T2.SCORE_id, T1.SumOfAmount;

Result
Status SCORE SumOfAmount
Chief Excellent 1
Chief Fair 1
Chief Good 2
DD Fair 3
DD Good 4
DD Poor 1
DMG Fail 1
DMG Fair 5
DMG Good 4
DMG Poor 2
MGR Fail 5
MGR Fair 7
MGR Good 2
MGR Poor 2
Supervisor Excellent 5
Supervisor Fair 2
Supervisor Good 5

*********************************************

How can I fix this problem?

Thanks in advance

 

[Guest]

I am not sure what your question is? ... and what needs to be fixed?

In the sample data you posted, every entry in T2 has some matching entries
in T1 so the return dataset from the Left Outer Join is exactly the same with
the returned dataset from an inner join.

Post what you want the returned dataset to be w.r.t. your sample data.

 

[John Spencer]

I think what you want is one row for each combination of Status_Id and
Score_Id. You did not say what result you want, just what result you are
getting.

Multiple query solution.

First get the combination of all scores and status, save that as qItems
SELECT Distinct T1.Status_ID, T2.Score_ID
FROM T1, T2

Now use that result in your second query.
SELECT qItems.Status_ID
, qItems.Score_ID
, SumOfAmount
FROM qItems LEFT JOIN T1
ON qItems.StatusID = T1.StatusId AND
qItems.ScoreID = T1.ScoreID

If you need to do this in one query, try
SELECT qItems.Status_ID, qItems.Score_ID, SumOfAmount
FROM (
SELECT Distinct T1.Status_ID, T2.Score_ID
FROM T1, T2) as qItems
LEFT JOIN T1
ON qItems.StatusID = T1.StatusId AND
qItems.ScoreID = T1.ScoreID

If you want SumOfAmount to show zero, then use the NZ function to force a
zero to be returned
Nz(SumOfAmount,0) as Amount

 

[Guest]

Hi all

My requirement is this below

Result
Status SCORE SumOfAmount
Chief Excellent 1
Chief Fail 0
Chief Fair 1
Chief Good 2
Chief Poor 0
DD Excellent 0
DD Fail 0
DD Fair 3
DD Good 4
DD Poor 1
DMG Excellent 0
DMG Fail 1
DMG Fair 5
DMG Good 4
DMG Poor 2
MGR Excellent 0
MGR Fail 5
MGR Fair 7
MGR Good 2
MGR Poor 2
Supervisor Excellent 5
Supervisor Fail 0
Supervisor Fair 2
Supervisor Good 5
Supervisor Poor 0

For John, I already try by following your advice.
It works and is very helpful.

Thanks for your reply again.

Gogzilla

Thanks for all your reply

Gogzilla

 

[Van T. Dinh]

You want a Cartesian Join to create all Status_id - Score_id pairs with zero
SumofAmount. You then need to union them with the existing pairs with
non-zero SumOfAmount and finally add then to get the result you want.

Try an SQL like (untested)

SELECT SQ.Status_id, SQ.SCORE_id, Sum(SQ.SumOfAmount) AS StatusScore

FROM
(
SELECT T1.Status_id, T2.SCORE_id, T1.SumOfAmount
FROM T1 INNER JOIN T2
ON T1.Score_id = T2.SCORE_id

UNION

SELECT T1_C2.Status_id, T2_C2.SCORE_id, 0
FROM T1 AS T1_C2, T2 AS T2_C2
) As SQ

GROUP BY SQ.Status_id, SQ.SCORE_id
ORDER BY SQ.Status_id, SQ.SCORE_id;

There may be other methods ...

 

[Guest]

Hi Dinh

After I test the script, the result is OK and same as John's advice.

Thaks for your reply again

Gogzilla