Multiple exists not functioning as expected?

I have the following query.
declare @.p0 nvarchar(6)
declare @.p1 nvarchar(2)
set @.p0 = 'Trish%'
set @.p1 = 'SH'
SELECT [t1].[DocumentId], [t1].[Version], [t1].[ClosedDate] AS
[ClosedDateUTC], [t1].[DueDate] AS [DueDateUTC], [t1].[DueDateLatest]
AS [DueDateLatestUTC], [t1].[PriceListId], [t1].[SoldToId], [t1].
[TaxRate], [t1].[IsLocked], [t1].[ShippingCost], [t1].
[DocumentNumber], [t1].[RevisionNumber], [t1].[CreatedDate] AS
[CreatedDateUTC], [t1].[CreatedById], [t1].[ModifiedDate] AS
[ModifiedDateUTC], [t1].[ModifiedById], [t1].[TermsId], [t1].
[ShipMethodId], [t1].[FOBId], [t1].[DocumentStatusId], [t1].
[DocumentType], [t1].[CustPO], [t1].[DeliveryWindowId], [t1].
[CategoryId], [t1].[SubCategoryId], [t1].[ShipDate] AS [ShipDateUTC],
[t1].[value] AS [Subtotal], [t1].[value2] AS [Tax]
FROM (
SELECT [t0].[DocumentId], [t0].[Version], [t0].[ClosedDate], [t0].
[DueDate], [t0].[DueDateLatest], [t0].[PriceListId], [t0].[SoldToId],
[t0].[TaxRate], [t0].[IsLocked], [t0].[ShippingCost], [t0].
[DocumentNumber], [t0].[RevisionNumber], [t0].[CreatedDate], [t0].
[CreatedById], [t0].[ModifiedDate], [t0].[ModifiedById], [t0].
[TermsId], [t0].[ShipMethodId], [t0].[FOBId], [t0].[DocumentStatusId],
[t0].[DocumentType], [t0].[CustPO], [t0].[DeliveryWindowId], [t0].
[CategoryId], [t0].[SubCategoryId], [t0].[ShipDate],
CONVERT(Decimal(29,4),[dbo].[GetDocumentSubTotal]([t0].[DocumentId]))
AS [value], CONVERT(Decimal(29,4),[dbo].[GetDocumentTaxTotal]([t0].
[DocumentId])) AS [value2]
FROM [dbo].[vDocument] AS [t0]
) AS [t1]
WHERE (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[vDocumentContact] AS [t2]
WHERE ([t2].[FirstName] LIKE @.p0) AND ([t2].[DocumentId] = [t1].
[DocumentId])
)) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[vDocumentContact] AS [t3]
WHERE ([t3].[ContactType] = @.p1) AND ([t3].[DocumentId] = [t1].
[DocumentId])
))
-- @.p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Trish%]
-- @.p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [SH]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.21022.8
The problem is that this query returns rows, and it doesn't seem that
it should (there are no DocumentContact rows that have a FirstName
like Trish and ContactType = 'SH').
I believe it's because the two Exists in the where clause.. I would
think that rows have to be returned from BOTH Exists clauses for any
rows to be returned... but it seems that if EITHER Exists returns
rows, the entire query returns rows.
Am I not understanding how EXISTS works?
Thanks
Andy
If I understand correctly, you only want to return the rows where at least
one row exists in vDocumentContact for that DocumentId where that one row
has both a FirstName like Trish and ContactType = 'SH'. Your query does not
do that. It returns the rows where at least one row exists in
vDocumentContact for that DocumentId which has a FirstName like Trish and at
least one row exists in vDocumentContact for that DocumentId which has
aContactType = 'SH', but, as you have written the query, they don't have to
be the same row. For example, suppose I have a table of orders,
Create Table #Orders (OrderID int, CustomerID int);
Insert #Orders (OrderID, CustomerID)
Select 1, 1
Union All Select 2, 20
Union All Select 3, 30;
and a table of order lines,
Create Table #OrderLines (OrderID int, ProductID int, Quantity int);
Insert #OrderLines (OrderID, ProductID, Quantity)
Select 1, 1, 5
Union All Select 1, 2, 20
Union All Select 2, 1, 15
Union All Select 2, 3, 10
Union All Select 1, 1, 3;
If I am looking for orders which have ordered more than 10 of ProductID 1,
the following query does not do what I want,
Select o.OrderID, CustomerID
From #Orders o
Where Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ProductID = 1)
And Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ol.Quantity > 10);
That query returns both Order 1 and Order 2 because they both meet the
condition that there is a row in #Orderlines with ProductID 1 and there is a
row in #Orderlines with Quantity > 10. But for Order 1, these aren't the
same row, so I don't want that order returned. Instead, the query should be
written as
Select o.OrderID, CustomerID
From #Orders o
Where Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ProductID = 1
And ol.Quantity > 10);
Which returns the desired result (only Order 2).
So if I understand what you want, you need to replace your two WHERE EXISTS
with one WHERE EXISTS which checks both conditions.
If that is not what you meant, please post sample tables and data and a
description of the results you want. See www.aspfaq.com/5006 for how to do
this or do something like I did above.
Tom
"Andy" <andyj@.med-associates.com> wrote in message
news:12be336f-baee-4501-90db-e1a05226fa9a@.d62g2000hsf.googlegroups.com...
>I have the following query.
> declare @.p0 nvarchar(6)
> declare @.p1 nvarchar(2)
> set @.p0 = 'Trish%'
> set @.p1 = 'SH'
> SELECT [t1].[DocumentId], [t1].[Version], [t1].[ClosedDate] AS
> [ClosedDateUTC], [t1].[DueDate] AS [DueDateUTC], [t1].[DueDateLatest]
> AS [DueDateLatestUTC], [t1].[PriceListId], [t1].[SoldToId], [t1].
> [TaxRate], [t1].[IsLocked], [t1].[ShippingCost], [t1].
> [DocumentNumber], [t1].[RevisionNumber], [t1].[CreatedDate] AS
> [CreatedDateUTC], [t1].[CreatedById], [t1].[ModifiedDate] AS
> [ModifiedDateUTC], [t1].[ModifiedById], [t1].[TermsId], [t1].
> [ShipMethodId], [t1].[FOBId], [t1].[DocumentStatusId], [t1].
> [DocumentType], [t1].[CustPO], [t1].[DeliveryWindowId], [t1].
> [CategoryId], [t1].[SubCategoryId], [t1].[ShipDate] AS [ShipDateUTC],
> [t1].[value] AS [Subtotal], [t1].[value2] AS [Tax]
> FROM (
> SELECT [t0].[DocumentId], [t0].[Version], [t0].[ClosedDate], [t0].
> [DueDate], [t0].[DueDateLatest], [t0].[PriceListId], [t0].[SoldToId],
> [t0].[TaxRate], [t0].[IsLocked], [t0].[ShippingCost], [t0].
> [DocumentNumber], [t0].[RevisionNumber], [t0].[CreatedDate], [t0].
> [CreatedById], [t0].[ModifiedDate], [t0].[ModifiedById], [t0].
> [TermsId], [t0].[ShipMethodId], [t0].[FOBId], [t0].[DocumentStatusId],
> [t0].[DocumentType], [t0].[CustPO], [t0].[DeliveryWindowId], [t0].
> [CategoryId], [t0].[SubCategoryId], [t0].[ShipDate],
> CONVERT(Decimal(29,4),[dbo].[GetDocumentSubTotal]([t0].[DocumentId]))
> AS [value], CONVERT(Decimal(29,4),[dbo].[GetDocumentTaxTotal]([t0].
> [DocumentId])) AS [value2]
> FROM [dbo].[vDocument] AS [t0]
> ) AS [t1]
> WHERE (EXISTS(
> SELECT NULL AS [EMPTY]
> FROM [dbo].[vDocumentContact] AS [t2]
> WHERE ([t2].[FirstName] LIKE @.p0) AND ([t2].[DocumentId] = [t1].
> [DocumentId])
> )) AND (EXISTS(
> SELECT NULL AS [EMPTY]
> FROM [dbo].[vDocumentContact] AS [t3]
> WHERE ([t3].[ContactType] = @.p1) AND ([t3].[DocumentId] = [t1].
> [DocumentId])
> ))
> -- @.p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Trish%]
> -- @.p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [SH]
> -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
> 3.5.21022.8
> The problem is that this query returns rows, and it doesn't seem that
> it should (there are no DocumentContact rows that have a FirstName
> like Trish and ContactType = 'SH').
> I believe it's because the two Exists in the where clause.. I would
> think that rows have to be returned from BOTH Exists clauses for any
> rows to be returned... but it seems that if EITHER Exists returns
> rows, the entire query returns rows.
> Am I not understanding how EXISTS works?
> Thanks
> Andy