Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Distinct Data

Status
Not open for further replies.

lavadan

Programmer
Dec 17, 2007
49
US
I am new bie to sql. Can anyone help me with the following question.

I have the following customer table with CustomerID as pk. Each CustomerID can have many ContactID’s . Atleast one ContactID for each customer should be billing contact.

I have to find the CustomerID’s for which none of the ContactID has billing contact(=0)

Here is the table structure:

CustomerID ContactID BillingContact EmailContact

001 1 0 1
001 2 1 1
001 3 1 0
002 1 0 0
002 2 0 1
003 1 1 0
003 2 0 0
004 1 0 0


In that case 002 and 004 should be the results.
 
How CustomerId is PK if you have many CustomerIds with the same value?

Code:
SELECT Customer.*
FROM Customer
INNER JOIN (SELECT CustomerId, 
                   MAX(BillingContact) AS BillingContact
            FROM Customer
            GROUP BY CustomerId) Tbl1
      ON Customer.CustomerId = Tbl1.CustomerId AND
         Tbl1.BillingContact = 0


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Or;
Code:
SELECT Customer.*
FROM Customer
LEFT JOIN Customer Tbl1 
          ON Customer.CustomerId = Tbl1.CustomerId AND
             Tbl1.BillingContact = 1
WHERE Tbl1.CustomerId IS NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
The CustomerID is the PK has many unique ContactID.

Your soultion worked. Thanks.
 
Then the PK is based on CustomerId AND ContactID, isn't it?
:)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top