sql server programming: Left join is equal to inner join? -- sql server programming

Left join is equal to inner join?

Gogzilla
10/31/2006 8:40:02 PM

sql server programming: 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?

RE: Left join is equal to inner join?

Omnibuzz
10/31/2006 9:58:01 PM

I don't see any poblem.. What is the output you are expecting?
--
-Omnibuzz

http://omnibuzz-sql.blogspot.com/

[quoted text, click to view]

RE: Left join is equal to inner join?

Gogzilla
10/31/2006 10:56:02 PM

Hi Omnibuzz

My expectation 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

Thanks in advance
Gogzilla

[quoted text, click to view]

RE: Left join is equal to inner join?

Omnibuzz
10/31/2006 11:29:01 PM

What you are looking for is a cross product with a filter.
This should work

SELECT T0.Status_id, T2.SCORE_id, isnull(T1.SumOfAmount,0)
FROM
(
(select distinct t1.status_id from t1) t0
cross join
T2)
LEFT JOIN T1
ON T2.SCORE_id = T1.Score_id
and t0.status_id = t1.status_id
ORDER BY T0.Status_id, T2.SCORE_id, T1.SumOfAmount;

And here is a wierd ass solution (one join less) that will work too (adding
just for fun) but I don't know when it will break.. you may want to analyze
it and decide...

select t1.status_id,t2.score_id,
sum(case when (t1.score_id = t2.score_id)
then t1.sumofamount else 0 end) as sumofamount
from t1,t2
group by t1.status_id,t2.score_id
order by t1.status_id,t2.score_id

Hope this helps
--
-Omnibuzz

http://omnibuzz-sql.blogspot.com/

RE: Left join is equal to inner join?

Gogzilla
11/1/2006 4:45:01 PM

Hi Omnibuzz

It works and is very helpful.
Thanks for your reply.

Gogzilla

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.