Join multiple table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join multiple table

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-23 : 00:32:48

Dear All,

I tried following quearies and output i got below the queary.
Now i want both the queary output i want to combine.

Queary 1:

select I4.DocEntry,MAX(ot.AbsId) as AbsId, oa.Name, sum(I4.TaxSum) as TaxAmount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '91'
group by oa.Name,I4.DocEntry order by max(ot.absid)

Output 1:
DocEntry AbsId Name TaxAmount
91 7 Excise 30605.940000
91 8 ECESS 612.120000
91 9 HCESS 306.060000
91 13 OCTROI 10655.430000
91 14 OCT_SER 106.560000
91 15 VAT_4 14091.300000

Queary 2:

select i2.DocEntry,i2.ExpnsCode As AbsId,ox.ExpnsName As Name,i2.LineTotal As TaxAmount from inv2 i2
left join oexd ox on ox.ExpnsCode = i2.ExpnsCode
where i2.DocEntry in
(select DocEntry from inv4 where DocEntry = '91')

Output 2:

DocEntry AbsId Name TaxAmount
91 1 Packing Charges 2316.000000
91 2 Transportion Charge 3937.000000

Now i want following output.
Can anybody tell me how to write queary for that.

DocEntry AbsId Name TaxAmount
91 1 Packing Charges 2316.000000
91 2 Transportion Charge 3937.000000
91 7 Excise 30605.940000
91 8 ECESS 612.120000
91 9 HCESS 306.060000
91 13 OCTROI 10655.430000
91 14 OCT_SER 106.560000
91 15 VAT_4 14091.300000

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-23 : 00:43:26

use UNION ALL betwenn these 2 queries,,,,

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-23 : 00:47:34

Pal,

You are almost there...just combine the output of the two queries by using Union operator.

regards,
Anil Kumar.

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-23 : 00:56:05

Dear Freind,

I already tried but it gives error. I am getting exactly where this is error.

Error:

Incorrect syntax near the keyword 'union'.

Tried queary:

select I4.DocEntry,MAX(ot.AbsId) as AbsId, oa.Name, sum(I4.TaxSum) as TaxAmount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '91'
group by oa.Name,I4.DocEntry order by max(ot.absid)

union all

select i2.DocEntry,i2.ExpnsCode As AbsId,ox.ExpnsName As Name,i2.LineTotal As TaxAmount from inv2 i2
left join oexd ox on ox.ExpnsCode = i2.ExpnsCode
where i2.DocEntry in
(select DocEntry from inv4 where DocEntry = '91')

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-23 : 01:02:24

Just Post some sample data ...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-23 : 01:13:16

Try Like this may be...

select DocEntry,AbsId,Name,TaxAmount from
(
select I4.DocEntry,MAX(ot.AbsId) as AbsId, oa.Name, sum(I4.TaxSum) as TaxAmount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '91'
group by oa.Name,I4.DocEntry

union all

select i2.DocEntry,i2.ExpnsCode As AbsId,ox.ExpnsName As Name,i2.LineTotal As TaxAmount from inv2 i2
left join oexd ox on ox.ExpnsCode = i2.ExpnsCode
where i2.DocEntry in (select DocEntry from inv4 where DocEntry = '91')
)t order by AbsId

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-23 : 01:36:51

Dear Freind,

Thnsk for the reply. Queary working fine and i got the following output.

91 1 Packing Charges 2316.000000
91 2 Transportion Charge 3937.000000
91 7 Excise 30605.940000
91 8 ECESS 612.120000
91 9 HCESS 306.060000
91 13 OCTROI 10655.430000
91 14 OCT_SER 106.560000
91 15 VAT_4 14091.300000

Now last step i want show second entry(Transportation Charge) before OCTROI.
Can we show the specific entry on specific row.
Can u plz tell me how to this ?
I want following output,

91 1 Packing Charges 2316.000000
91 7 Excise 30605.940000
91 8 ECESS 612.120000
91 9 HCESS 306.060000
91 2 Transportion Charge 3937.000000
91 13 OCTROI 10655.430000
91 14 OCT_SER 106.560000
91 15 VAT_4 14091.300000

Harish Patil

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-23 : 03:07:45

You can do, we have to control it by case clause,

do it like that in order by clause AbsId

order by case when AbsId in (1,7,8,9) then 1 else 2 end

hspatil31
Posting Yak Master

182 Posts

Posted - 2009-04-23 : 03:28:14

Dear Freind,

I tried ur solution but it gives the wrong output. Means it showing wrong order means missorder. Plz check it. i want only second record (Transportation Charge)before the OCTROI record.

Queary :

select DocEntry,AbsId,Name,TaxAmount from
(
select I4.DocEntry,MAX(ot.AbsId) as AbsId, oa.Name, sum(I4.TaxSum) as TaxAmount
from inv4 I4
left join ostt ot on ot.AbsId = I4.StaType
left join osta oa on I4.StaCode = oa.Code
where I4.DocEntry = '91'
group by oa.Name,I4.DocEntry

union all

select i2.DocEntry,i2.ExpnsCode As AbsId,ox.ExpnsName As Name,i2.LineTotal As TaxAmount from inv2 i2
left join oexd ox on ox.ExpnsCode = i2.ExpnsCode
where i2.DocEntry in (select DocEntry from inv4 where DocEntry = '91')
)t order by case when AbsId in (1,7,8,9) then 1 else 2 end

Output :

91 8 ECESS 612.120000
91 7 Excise 30605.940000
91 9 HCESS 306.060000
91 1 Packing Charges 2316.000000
91 2 Transportion Charge 3937.000000
91 14 OCT_SER 106.560000
91 13 OCTROI 10655.430000
91 15 VAT_4 14091.300000

Harish Patil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources