Not getting the desired result.

Question

Post reply

Not getting the desired result.

Jones'e

Right there with Babe

Points: 744
More actions
January 8, 2007 at 9:56 am

#114993

Can anyone see why this query is not give me only vendors that are fvtype = "MD" "Where exists(Select * From apvend Where fvtype = 'MD')"

Declare @StartD smalldatetime, @EndD smalldatetime

Set @StartD = '12/01/2006'

Set @EndD = '12/31/2006'

SELECT [rcmast].[fpono] as "Purchase Order Number"

      ,[rcitem].[fpoitemno]as "PO Line Item Release Number"

      ,[rcmast].[fcompany] as "Supplier Name"

      ,[rcitem].[fvendno] as "Supplier ID"

      ,[rcitem].[fpartno] as "Part Number"

      ,[rcitem].[fdescript] as "Part Description"

      ,[rcitem].[fmeasure] as "UoM"

      ,[rcitem].[fqtyrecv] as "Quantity Received"

      ,[rcmast].[fdaterecv] as " Date Received"

      ,poitem.flstpdate as "Date Due"

FROM [M2MDATA01].[dbo].[rcmast]

Join rcitem on rcmast.freceiver = rcitem.freceiver

Join poitem on rcmast.fpono = poitem.fpono and poitem.fitemno = rcitem.fpoitemno and poitem.frelsno = rcitem.frelsno

Where exists(Select * From apvend   Where fvtype = 'MD')

and fdaterecv between @StartD and @endD and docstatus = 'Received' and rcitem.ftype = 'P'



order by [rcitem].[fvendno]--rcmast.fpono

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

January 8, 2007 at 10:01 am

#681826

This is the main problem

Where exists(Select * From apvend Where fvtype = 'MD')

It will always return true or always return false because fvtype = 'MD' will also be true or false, not one or the other.

You either need to correlate the exists clause or use one more inner join to set that filter.

Jones'e Right there with Babe Points: 744 More actions · Answer 2

Thanks for the help!!

Could you give me an example on using in a inner join?

Here is want I think you mean by correlate. Let me know if I am right.

Where [rcitem].[fvendno] in (Select fvendno From apvend Where fvtype = 'MD') and [rcmast].[fdaterecv] between @StartD and @endD and rcmast.docstatus = 'Received' and rcitem.ftype = 'P'

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

January 8, 2007 at 11:21 am

#681845

I'm not sure I'm following and I don't want to guess at this one.

Can you post the tables ddl for the 2 tables concerned, some sample data from both tables and the required output. Clearly stating what data must be presented and what data must be filtered?