I got the result

select mfr.company,mfr.companycode,prd.productid, prd.itemcode,
otr.price as price from manufacturer mfr, product_tab prd, otheritems
otr where otr.itemcode in (select prd2.itemcode from otheritems otr,
Product_tab prd2 where prd2.itemcode=otr.itemcode and otr.price in
(select fd.price from Product_tab prd1, fooditems fd where
prd1.itemcode = fd.itemcode and prd1.productid = prd2.productid and
prd2.companycode = prd1.companycode)) and prd.itemcode=otr.itemcode and
prd.companycode=mfr.companycode 
union all 
select mfr.company,mfr.companycode,prd.productid, prd.itemcode,
fd.price as price from manufacturer mfr, product_tab prd, fooditems fd
where fd.itemcode in (select prd2.itemcode from fooditems fd,
Product_tab prd2 where prd2.itemcode=fd.itemcode and fd.price in
(select otr.price from Product_tab prd1, otheritems otr where
prd1.itemcode = otr.itemcode and prd1.productid = prd2.productid and
prd2.companycode = prd1.companycode)) and prd.itemcode=otr.itemcode and
prd.companycode=mfr.companycode

--- David Cameron <dcameron@i...> wrote:
> If I understand the problem correctly this should do you.
> 
> SELECT M.CompanyCode, Company, ProductID, ItemCode, Price
> FROM item_code IT
>     INNER JOIN [Food Items] FI ON		-- bad table name
>     FI.Item_code = IT.Item_code
>     AND FI.Price = IT.Price
>     INNER JOIN Product_tab PT ON
>     PT.Item_code = IT.item_code
>     INNER JOIN Manufacturer M ON
>     M.CompanyCode = PT.CompanyCode
> 
> On the other hand if you want records displayed multiple times you
> may need to move to subqueries to perform your logic (instead of in
> the FROM clause).
> 
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
> 
> > -----Original Message-----
> > From: Shankar [mailto:shankargo@y...]
> > Sent: Thursday, 12 December 2002 1:45 PM
> > To: sql language
> > Subject: [sql_language] need solution for this
> > 
> > 
> > Hi,
> > 
> > Table: Manufacturer
> > Companycode	Company
> > 2		P&G
> > 4		Nestle
> > 5		Cadbury
> >  
> > Table: Product_tab
> > Companycode	ProductID	Item_code
> > 2		1234		123456
> > 2		1234		123457
> > 4		1234		123458
> > 4		1234		123459
> > 5		1234		123460
> > 5		2345		123461
> > 5		2345		123462
> > 5		2345		123463
> >  
> > Table: Food Items
> > Item_code	Price
> > 123456		100
> > 123457		100
> > 123458		500
> > 123460		200
> > 123462		400
> >  
> > Table: OtherItems
> > Item_code	Price
> > 123459		500
> > 123461		100
> > 123463		200
> > 
> > Output:-
> > Companycode	Company	ProductID	ItemCode	Price
> > 4		Nestle	1234		123458		500
> > 4		Nestle	1234		123459		500
> > 5		Cadbury	2345		123460		200
> > 5		Cadbury	2345		123463		200
> >  
> > Requirement:- If the price of the items in table-food items and
> > table-otheritems are equal and if they belong to the same productid
> > and companycode, then we have to list them in the output.
> > Item code is unique.
> > 
> > Regards,
> > Shankar
> > 
> > __________________________________________________
> > Do you Yahoo!?
> > Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> > http://mailplus.yahoo.com
> > 
> > 
> 


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com