 |
|
|
|
Next: Search database for field containing value
|
| Author |
Message |
External
Since: Aug 13, 2007
Posts: 2
|
(Msg. 1) Posted: Tue Oct 31, 2006 8:40 pm
Post subject: Left join is equal to inner join?
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
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 |
|
| Back to top |
|
 | |
External
Since: Jul 19, 2006
Posts: 11
|
(Msg. 2) Posted: Tue Oct 31, 2006 9:58 pm
Post subject: RE: Left join is equal to inner join? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
I don't see any poblem.. What is the output you are expecting?
--
-Omnibuzz
http://omnibuzz-sql.blogspot.com/
"Gogzilla" wrote:
> 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 |
|
| Back to top |
|
| |
External
Since: Aug 13, 2007
Posts: 2
|
(Msg. 3) Posted: Tue Oct 31, 2006 10:56 pm
Post subject: RE: Left join is equal to inner join? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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
"Omnibuzz" wrote:
> I don't see any poblem.. What is the output you are expecting?
> --
> -Omnibuzz
>
> http://omnibuzz-sql.blogspot.com/
>
>
>
> "Gogzilla" wrote:
>
> > 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 >> Stay informed about: Left join is equal to inner join? |
|
| Back to top |
|
| |
| Related Topics: | left join/inner join/outer join with repeat values - hi david, thanks for the reply. basically what i need this for is: there are two tables: table1 is a master table, table2 is a detail table. table1's primary_id is present in details_id of table2 table1 has only 1 record table2 could have many..
Left join - On a Sql Server 2005, suppose the query SELECT ...... FROM A LEFT JOIN B ON B.Col1 = A.Col2 LEFT JOIN C ON (C.Col3 = A.Col4 OR C.Col5 = B.Col6) If the left join on B results in 1 or more rows, then I get all the rows from C: the rows that apply to the....
LEFT JOIN question - Hi, I have table A and I create a left join to table B: SELECT * from tableA a LEFT JOIN tableB b ON a.id=b.id... and I get all the records from table A, that is 100 records for example. If I create join to table C: SELECT * from tableA a LEFT JOIN....
use "left join" or "not exists" ? - Using SQL Server 2000, Example : == create table jtest1 (id int primary key, col1 varchar(10)) create table jtest2 (id2 int primary key, col2 varchar(10)) insert jtest1 values (1, 'hello') insert jtest1 values (2, 'two') insert jtest2 values (1,..
LEFT OUTER JOIN problem - Hello, I have 2 tables , tblTOSub , and tblTrScanSub with this schema , the 2 identities are the PKeys. CREATE TABLE [dbo].[tblTOSub]( [TOSubID] [int] IDENTITY(1,1) NOT NULL, [TOID] [int] NOT NULL, [ItemID] [int] NOT NULL, [Qty] [int] NOT NULL, .. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
