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

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
AndyIf 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
> Andysql

Multiple DECLARE Sections ... why?

Is there any value to having multiple DECLARE sections within a single store
d
proc/statement block? I understand the value of multiple DECLARE sections
that are related to local variable within specific statement blocks.
However, in working with existing SP's, I run across the following on a
regular basis and would like to better understand if this structure provides
any value and/or cost to the proc's effeciency.
Thanks in advance for your insight/feedback.
Example:
CREATE PROC test
AS
DECLARE
@.var1 int,
@.var2 int
DECLARE
@.text1 varchar(8),
@.text2 varchar(8)It makes no difference. SQL is interpreted, so this won't make any
difference at all to the execution of the SP.
It looks like it was done to aid reading the code, all variables of the same
type declare together.
Regards
Colin Dawson
www.cjdawson.com
"Tom B." <TomB@.discussions.microsoft.com> wrote in message
news:74FD5973-B982-4C06-92F9-3DEAB4879109@.microsoft.com...
> Is there any value to having multiple DECLARE sections within a single
> stored
> proc/statement block? I understand the value of multiple DECLARE sections
> that are related to local variable within specific statement blocks.
> However, in working with existing SP's, I run across the following on a
> regular basis and would like to better understand if this structure
> provides
> any value and/or cost to the proc's effeciency.
> Thanks in advance for your insight/feedback.
> Example:
> CREATE PROC test
> AS
> DECLARE
> @.var1 int,
> @.var2 int
> DECLARE
> @.text1 varchar(8),
> @.text2 varchar(8)
>|||Sorry for any confusion, but the example was just that ... a basic
representation of the code that I've seen (actual code doesn't appear to hav
e
much rhyme or reason to the variable groupings). As for its value with
making the code more user friendly to read ... that makes perfect sense.
Thanks for the quick feedback!
"Tom B." wrote:

> Is there any value to having multiple DECLARE sections within a single sto
red
> proc/statement block? I understand the value of multiple DECLARE sections
> that are related to local variable within specific statement blocks.
> However, in working with existing SP's, I run across the following on a
> regular basis and would like to better understand if this structure provid
es
> any value and/or cost to the proc's effeciency.
> Thanks in advance for your insight/feedback.
> Example:
> CREATE PROC test
> AS
> DECLARE
> @.var1 int,
> @.var2 int
> DECLARE
> @.text1 varchar(8),
> @.text2 varchar(8)
>