Query/SQL help

[Joe O]

I have two tables

Table1
Item# -Descr-Amt
xyz1- Orange - 250.00
xyz2 - Apple - 350.00

Table 2
Item#-Tax-Amt
xyz1-Tax1- 2.50
xyz1-Tax2-3.50
xyz2-Tax1-3.00
xyz2-Tax2-4.20

I will like have a query to show data as
Item#-Descr-Amt-Tax1-Tax2
xyz1-Orange-250.00-2.50-3.50
xyz2-Apple-350.00-3.00-4.20

Thanks for any help.

 

[Adam Machanic]

http://www.aspfaq.com/show.asp?id=2462

 

[Guest]

Try this query

select a.Item# , Descr , a.Amt , b.Amt as Tax1 , ( select e.Amt
from
table1 d ,
table2 e
where

d.Item# = e.Item#
and Tax = 'Tax2' and d.Item#
= a.Item#
and a.Descr = d.Descr
) as Tax2
from table1 a ,
table2 b
where a.Item# = b.Item# and Tax = 'Tax1'

 

[Joe O]

Thanks Adam,Tarik... I am checking out the tips

Try this query

select a.Item# , Descr , a.Amt , b.Amt as Tax1 , ( select e.Amt
from
table1 d ,
table2 e
where

d.Item# = e.Item#
and Tax = 'Tax2' and d.Item#
= a.Item#
and a.Descr = d.Descr
) as Tax2
from table1 a ,
table2 b
where a.Item# = b.Item# and Tax = 'Tax1'