Query/SQL help

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.http://www.aspfaq.com/show.asp?id=2462
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe O" <jp3BlessNoSpam@.hotmail.com> wrote in message
news:OpFicqj2FHA.3272@.TK2MSFTNGP09.phx.gbl...
> 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.
>
>|||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" wrote:

> 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.
>
>|||Thanks Adam,Tarik... I am checking out the tips
"Tarik" <Tarik@.discussions.microsoft.com> wrote in message
news:2333E900-BF9F-4C43-BE24-02AD2DAB4650@.microsoft.com...
> 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" wrote:
>