SQL Server

[venky.skcet]

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 :?:

[ChrisPadgham]

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

[BitBlt]

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!

[ChrisPadgham]

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. :-)

[venky.skcet]

thank you i got it :cool: