Distinct Data

[lavadan]

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.

 

[bborissov]

How CustomerId is PK if you have many CustomerIds with the same value?

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.

 

[bborissov]

Or;

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.

 

[lavadan]

The CustomerID is the PK has many unique ContactID.

Your soultion worked. Thanks.

 

[bborissov]

Then the PK is based on CustomerId AND ContactID, isn't it?


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.