Same table twice left join issue

Question

Same table twice left join issue

s.chandrahasan

SSC Eights!

Points: 858
More actions
December 3, 2013 at 1:40 am

#301922

Hi All ,
I have table with data like below

Namemarksmonthsubject
Kumar801maths
Guna701science
Kumar502physics
Guna402science
Query
select t1.name,t1.marks-t2.marks as Marks,CONVERT(VARCHAR(19),
t1.month)+'~'+CONVERT(VARCHAR(19),
t2.month) as Month,
t1.subject from #tmp_h t1
left join #tmp_h t2 on t1.name=t2.name and t1.subject=t2.subject
where t1.month<>t2.month
Actual output
nameMarksMonthsubject
Guna301~2science
Guna-302~1science
I need output like below.
nameMarksMonthsubject
Kumar801~2maths
Kumar402~1science
Guna301~2science
Guna-302~1science
Kumar801~2maths
Kumar402~1science
these rows not added , I need these details also .. Kindly advise what need to change in the query

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

My psychic abilities are failing me... could you explain what your goal is? Looks like you're trying to calculate a change in grades for a single (student,course) between two time periods.

s.chandrahasan SSC Eights! Points: 858 More actions · Answer 2

Hi pietlinden ,

I need to find the variance between month 1 and 2

For Name Guna in month 1 70 marks in science subject

For Name Guna in month 2 40 marks in science subject

so variance about month 1 and 2 is

Guna301~2science

so variance about month 2 and 1 is

Guna-302~1science

here is issue

For Name Kumar in month 1 80 marks in maths subject

For Name Kumar in month 2 have no maths subject

but i need to calculate 2 month marks as 0

80-0 =80 Marks

so variance about month 1 and 2 is

Kumar 80 1~2Maths

Can you understand now ..I need to find the variance between the month of marks

Thanks,

Chandrahasan S

ParulPrabu SSCrazy Points: 2271 More actions · Answer 3

You have used left join for table x and you are applying the WHERE clause.

Even your query gets the data, it will not meet the WHERE clause condition, so those rows will be removed from the result.

ParulPrabu SSCrazy Points: 2271 More actions · Answer 4

Try with this code

WITH DATA (Name,marks,month,subject) AS

(SELECT 'Kumar',80,1,'maths' UNION

SELECT 'Guna',70,1,'science' UNION

SELECT 'Kumar',40,2,'physics' UNION

SELECT 'Kumar',70,3,'physics' UNION

SELECT 'Guna',40,2,'science')

select t1.name,ISNULL(t1.marks,0)-ISNULL(t2.marks, 0) as Marks,

CONVERT(VARCHAR(19),ISNULL(t1.month, '')

)+'~'+CONVERT(VARCHAR(19),

case when t2.month is null and t1.month=2 then t1.month - 1

when t2.month is null and t1.month=1 then 2 else t2.month end) as Month,

t1.subject from DATA t1

left join DATA t2 on t1.name=t2.name and t1.subject=t2.subject

AND t1.month<>t2.month

s.chandrahasan SSC Eights! Points: 858 More actions · Answer 5

HI parulprabu ,

Thanks for your reply .

Almost correct output

nameMarksMonthsubject

Kumar801~0maths

Guna301~2science

Kumar502~0physics

Guna-302~1science

BUT, Is possible to show the actual month 1~2 instead of 1~0

I have to validate with Month column after words

Thanks,

ParulPrabu SSCrazy Points: 2271 More actions · Answer 6

Assuming that, month 2 is having data and 1 is not having data for 2~1

and month 1 is having data and month 2 is not having data for 1~2

ParulPrabu SSCrazy Points: 2271 More actions · Answer 7

Hi,

I have edited the query based on the assumption mentioned above.

s.chandrahasan SSC Eights! Points: 858 More actions · Answer 8

Hi parulprabu ,

Thanks for reply again ,

Actually in month column does not has in order 1,2,3,4...

It may be some time month column M1,M2,M3,M4 .etc

so case condition not suitable for this

case when t2.month is null and t1.month=2 then t1.month - 1

when t2.month is null and t1.month=1 then 2 else t2.month end)

Is possible without hard coding ?

David Burrows SSC Guru Points: 65142 More actions · Answer 9

For your data containing 2 months

;WITH

m (Month1, Month2)

AS (

SELECT MIN(Month), MAX(Month)

FROM #tmp_h

UNION ALL

SELECT MAX(Month),MIN(Month)

FROM #tmp_h

),

n (name, subject)

AS (

SELECT name, subject

FROM #tmp_h

GROUP BY name, subject

)

SELECTn.name,

t1.Marks-ISNULL(t2.Marks,0) AS [Marks],

CAST(m.Month1 as varchar(2))+'~'+CAST(m.Month2 as varchar(2)) AS [month],

n.subject

FROMn

CROSS JOIN m

JOIN #tmp_h t1 ON t1.name = n.name AND t1.subject = n.subject AND t1.Month = m.Month1

LEFT JOIN #tmp_h t2 ON t2.name = n.name AND t2.subject = n.subject AND t2.Month = m.Month2

ORDERBY n.name ASC, m.Month1 ASC

How many months are you catering for? and are they consecutive?

Far away is close at hand in the images of elsewhere.
Anon.