display on duplicate records

I have the following query:
SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
FROM dbo.cbt_BillingAddress
WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
ORDER BY ProductCode, BillingCustID, BillingPeriod
which returns the following columns:
BillingPeriod CustomerID ProductCode BillingCustID
-- -- -- --
200502 205022338 APX 79110
200503 205022338 APX 79110
200504 205022338 BRW 79110
200505 205022338 BRW 79110
200506 205027355 APX 79110
200506 205022338 APX 79110
200507 205027355 BRW 79110
200507 205022338 BRW 79110
As you can see from the result, there are duplicates under the billingperiod
column for the same billingcustID. Essentially, there should only ever be on
e
CustomerID associated with the same ProductCode and BillingCustID and
BillingPeriod.
How can I rewrite my query to only display only the duplicates?
Thanks for your help in advance.Try this...
SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
FROM dbo.cbt_BillingAddress A
WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
AND EXISTS (
SELECT 1 FROM dbo.cbt_BillingAddress B
WHERE A.BillingCustID = B.BillingCustID
AND A.ProductCode = B.ProductCode
AND A.BillingPeriod = B.BillingPeriod
AND A.CustomerID <> B.CustomerID)
ORDER BY ProductCode, BillingCustID, BillingPeriod
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:D1DDD109-45A5-479C-B784-EE86856B14B0@.microsoft.com...
> I have the following query:
> SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
> FROM dbo.cbt_BillingAddress
> WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
> ORDER BY ProductCode, BillingCustID, BillingPeriod
> which returns the following columns:
> BillingPeriod CustomerID ProductCode BillingCustID
> -- -- -- --
> 200502 205022338 APX 79110
> 200503 205022338 APX 79110
> 200504 205022338 BRW 79110
> 200505 205022338 BRW 79110
> 200506 205027355 APX 79110
> 200506 205022338 APX 79110
> 200507 205027355 BRW 79110
> 200507 205022338 BRW 79110
> As you can see from the result, there are duplicates under the
billingperiod
> column for the same billingcustID. Essentially, there should only ever be
one
> CustomerID associated with the same ProductCode and BillingCustID and
> BillingPeriod.
> How can I rewrite my query to only display only the duplicates?
> Thanks for your help in advance.|||Awesome. Thanks for your help. One last question: what is the significance o
f
the statement:
SELECT 1...
What does this do and where can I get more info on its usage. Thanks again.
"Jim Underwood" wrote:

> Try this...
> SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
> FROM dbo.cbt_BillingAddress A
> WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
> AND EXISTS (
> SELECT 1 FROM dbo.cbt_BillingAddress B
> WHERE A.BillingCustID = B.BillingCustID
> AND A.ProductCode = B.ProductCode
> AND A.BillingPeriod = B.BillingPeriod
> AND A.CustomerID <> B.CustomerID)
> ORDER BY ProductCode, BillingCustID, BillingPeriod
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:D1DDD109-45A5-479C-B784-EE86856B14B0@.microsoft.com...
> billingperiod
> one
>
>|||Honestly, I am not sure if this serves a purpose in SQL Server, but I use it
out of habit from my Oracle 7/8 experience.
where exists (Select 1 from table1 where column1 = 'MyData')
is functionally the same as
where exists (Select column1 from table1 where column1 = 'MyData')
It simply verifies that a row exists in either case. I use the literal 1
for performance reasons.
In Oracle 7/8 (and likely 9 and 10) selecting a literal uses less memory
than selecting a value from a table. You could select a character ('x' for
example) but I was always told using a number was more efficient than a
character. Essentially, you don't need to retrieve a data value from disk
or memory, save what you use in your where clause. The SQL engine can
evaluate the where clause without returning any data in the process.
SQL server may very well ignore the select columns in an exist clause, I
really don't know. Maybe someone with more experience can validate or
correct me on this point.
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:0155F28A-3A76-4159-8157-EFC6E2A45C0E@.microsoft.com...
> Awesome. Thanks for your help. One last question: what is the significance
of
> the statement:
> SELECT 1...
> What does this do and where can I get more info on its usage. Thanks
again.
>
> "Jim Underwood" wrote:
>
be|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uCH2N0pJGHA.984@.tk2msftngp13.phx.gbl...
> Honestly, I am not sure if this serves a purpose in SQL Server, but I use
> it
> out of habit from my Oracle 7/8 experience.
> where exists (Select 1 from table1 where column1 = 'MyData')
> is functionally the same as
> where exists (Select column1 from table1 where column1 = 'MyData')
> It simply verifies that a row exists in either case. I use the literal 1
> for performance reasons.
> In Oracle 7/8 (and likely 9 and 10) selecting a literal uses less memory
> than selecting a value from a table. You could select a character ('x'
> for
> example) but I was always told using a number was more efficient than a
> character. Essentially, you don't need to retrieve a data value from disk
> or memory, save what you use in your where clause. The SQL engine can
> evaluate the where clause without returning any data in the process.
> SQL server may very well ignore the select columns in an exist clause, I
> really don't know. Maybe someone with more experience can validate or
> correct me on this point.
I'm sure that I don't have more experience but from what I've seen in this
newsgroup,
select *
is the norm in Exists clauses.