0

I am working with a database application and am currently having a problem in joining the tables..

Table 1 : Materials -- Columns (item_code PK,item_name,p_rate,n_rate)

Table 2 : Projects -- Columns (proj_id PK,proj_name,etc,etc)

Table 3 : Proj_Mat -- Columns (id PK,item_code FK,proj_id FK,qty)

Here Proj_Mat works as the bridge table.. My question is i want the result table as follows when i give the proj_name as input :

Result Table Columns :
item_name, qty

Pls help me with this asap :?:

3
Contributors
4
Replies
5
Views
5 Years
Discussion Span
Last Post by venky.skcet
0
SELECT Materials.Item_Name, Proj_Mat.qty FROM Materials, Proj_Mat WHERE 
Materials.Item_Code=Proj_Mat.Item_Code AND Proj_Mat.Proj_Id=CurrentProjId

CurrentProjId is obviously the project you wish to report on

0

Hmm...not quite. The OP said to use Project Name, not ID.

I think this might do the trick:

select b.item_name, a.qty
from dbo.Proj_Mat a
inner join dbo.Materials b
on a.item_code = b.item_code
inner join dbo.Projects c
on a.proj_id = c.proj_id
where c.proj_name = @myProjName

...where @myProjName is, of course, the name of a variable which contains the project you want.

Personally, I prefer to use the explicit "JOIN" syntax, although either form will work.

Hope this helps! Good luck!

0

oops, yes I missed that bit about the project name. I too prefer explicit joins laziness and too much time in the Oracle world must have overcome me. :-)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.