SELECT Statement

[Gav]

Hi all,

Wondering if I can get a little help?

I have three tables one of which is simply a link table, ie

CustomersTable
CustID
1
2

ContactsTable
ContactID
1
2

CustToContactsTable
CustID ContactID
1 1
1 2

Now lets say I have linked these table together, and I am trying to SELECT *
FROM CustomersTable WHERE ContactsTable.ContactID = 1 or 2 etc.

The problem I am having is that I only want the customer to appear once. In
the above query customer 1 will be returned twice. I have tried changing the
types of links between the tables but it doesn't make any difference. Is
there a simple solution to this?

I realise I could run a query such as:

SELECT * FROM CustomersTable CT WHERE EXISTS (SELECT * FROM
CustToContactsTable CTC WHERE CT.CustID = CTC.CustID AND ContactID= 1 OR
ContactID=2)

something like that anyway but I was just wondering if there is a more
simple way of doing this by linking the tables together.

Thanks
Gav

 

[John Spencer]

Not a lot simpler, but since it doesn't use a coordinated sub-query this may
be a bit faster.

SELECT * FROM CustomersTable CT
WHERE CT.CustID IN
(SELECT CTC.CustID FROM
CustToContactsTable CTC
WHERE ContactID in (1,2))

And if you were just trying to get results and didn't need an updatable
query, you could always have used the DISTINCT keyword.

SELECT DISTINCT CT.*
FROM CustomersTable CT INNER JOIN CustToContactsTable CTC
ON CT.Custid = CTC.CustID
WHERE CTC.ContactID in (1,2)