hello i need help trying to pull information from 2 tables when the info in table 2 is only for some of the data in table 1. i'm not very good at explaining what i need help with but i'll try. i'm writing a query that shows the leadtime, how long it takes for a part to either be fully assembled or ship to us from a vendor. in our inmast table it shows all parts that we have and thier leadtime. in our apvend table that's where i get the vendor info. the only way to tell if a part is a purchase item or a make item in the inmast table is by a field called fcpurchase, it it's a purchase item it has a y if it doesn't a n. so i wrote a prelimanary code that just shows me the fpurchase field so i can see what parts are purchased, the code is as follows:

SELECT inmast.fpartno, inmast.fdescript, inmast.fleadtime,inmast.fcpurchase
FROM M2MDATA01.dbo.inmast inmast
WHERE (inmast.fleadtime>0)
order by inmast.fpartno

then after it got that i tried a case statement that said this:

SELECT inmast.fpartno, inmast.fdescript, inmast.fleadtime,inmast.fcpurchase,
case inmast.fcpurchase
when 'y' then invend.fvendno
else 'make'
end
FROM M2MDATA01.dbo.inmast inmast,invend
WHERE (inmast.fleadtime>0)
order by inmast.fpartno

with this code the vendno is just some random vendno from the table. it doesn't get the vendno that is corresponding with the partno. so then i tried a subquery inside my case statement like this:

SELECT inmast.fpartno, inmast.fdescript, inmast.fleadtime,inmast.fcpurchase,
case inmast.fcpurchase
when 'y' then (select invend.fvendno from invend,inmast where inmast.fpartno = invend.fpartno)
else 'make'
end
FROM M2MDATA01.dbo.inmast inmast,invend
WHERE (inmast.fleadtime>0)
order by inmast.fpartno

part that gives me an error that says:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

so my subquery is returning to many values. i tried the same code but instead of comparing the fcpurchase field i did the fpartno field saying:

case inmast.fpartno
when invend.fpartno then fvendno

this would work if i gave a specific partno like '20571':

case inmast.fpartno
when '20571' then fvendno

so if any of you are following along with this all i want this query to do is display all the parts in the inmast table that have leadtimes greater than 0 and then if they are puchase items show the vendno from the invend table.i thought my case statements would work but for some reason the one isn't connecting the two partno's and the other the subquery returns to many values

any ideas are much appreciated.
hopefully this is clearer than my last post, if you need me to clear up anything just post it and i'll do my best

jb