I have 3 tables.

Table 1:

Class             Total No. of Students

X                        100

Y                         100

Table 2:

Class             No. of Students Attendance

X                        100

Y                          80

Table 3:

Class             No. of Students Missing

Y                          20

Question:

When I am writing the inner join I am getting the result as below.

Class         Total No. of Students      Attended       Missing

Y                     100                            80                20

I want the result as below

Class         Total No. of Students      Attended       Missing

X                    100                            100                 

Y                     100                            80                20

Can someone please help?

Thanks,

Phani Kumar

Hi,

you have to use left join instead of inner join.

• Proposed as answer by pituachModerator 17 hours 17 minutes ago

Table 1:#t111

elementValue                date                               LoadedinCD
10141                          08/21/2015                          368
10951                          08/21/2015                           16

Table 2:#t222

elementValue                 date                          LoadedtoKD
10141                           08/21/2015                       355
10951                           08/21/2015                         16

Table 3: #t333

elementValue                  date                           MissedtoLoadtoKD
10141                            08/21/2015                        13

 

Query used:

select

a.*,LoadedtoKEYDEVS,MissedtoLoadtoKEYDEVS from #t111 a(nolock)

inner join #t222 b(nolock) on a.elementValue=b.elementValue

inner join #t333 c(nolock) on c.elementValue=a.elementValue

where

a.date=b.date and b.date=c.date

I am getting the result as below

elementValue          date             LoadedinCD         LoadedtoKD            MissedtoLoadtoKD
10141                08/21/2015           368                     355                             13

I want the result as below

elementValue          date             LoadedinCD         LoadedtoKD            MissedtoLoadtoKD
10141                08/21/2015           368                     355                             13

 10951               08/21/2015           16                         16

Have you read Steelleg4 response?

He gave you the answer above

Please check this link as well: http://www.w3schools.com/sql/sql_join.a

Hi,

you have to use left join instead of inner join.

• Proposed as answer by pituachModerator Saturday, August 29, 2015 1:51 PM

Hi Phani,

Can you check this.

select * from Table_1
select * from Table_2
select * from Table_3

select T1.Class,T1.Students,T2.StudentsAttended,T3.StudentsMissing from Table_1 T1
inner join Table_2 as T2
on T1.Class=T2.Class 
left join Table_3  T3
on T2.Class=T3.Class 

Thanks, SMSVikasK

• Proposed as answer by Naomi NModerator 3 hours 47 minutes ago

Hi Phani,

Can you check this.

select * from Table_1
select * from Table_2
select * from Table_3

select T1.Class,T1.Students,T2.StudentsAttended,T3.StudentsMissing from Table_1 T1
inner join Table_2 as T2
on T1.Class=T2.Class 
left join Table_3  T3
on T2.Class=T3.Class 

Thanks, SMSVikasK

• Proposed as answer by Naomi NModerator Sunday, August 30, 2015 3:21 AM

Hi Phani,

The #t333 seems to be redundant, is there any case that "a.LoadedKD - b.LoadedKD" in below query show inconsistency to MissedtoLoadtoKD in #t333?

CREATE TABLE #t111
(
elementValue  INT,
[date] date,
LoadedinCD INT
);

CREATE TABLE #t222
(
elementValue  INT,
[date] date,
LoadedinCD INT
);

INSERT INTO #t111 VALUES
(10141,'08/21/2015',368),
(10951,'08/21/2015',16)

INSERT INTO #t222 VALUES
(10141,'08/21/2015',355),
(10951,'08/21/2015',16)

select
a.*,b.LoadedinCD LoadedtoKD,a.LoadedinCD-b.LoadedinCD MissedtoLoadtoKD from #t111 a(nolock)
inner join #t222 b(nolock) on a.elementValue=b.elementValue and a.date=b.date

If you have any question, feel free to let me know.