Problem with a SQL Statement

I have three tables:
Products Table
PID
PName
PType
Linking Table
LID
PID
CID
Customers Table
CID
CName
CPhone
I am trying to make a single statement that will give me all the customers
that haven't bought a certain single product so i can make a list of people
to contact regarding that product.
Any help or suggestions would be greatly appreciated... I just can't seem to
get this one right.
@.SELECT c.CName, c.Cphone
FROM Customers c
LEFT OUTER JOIN
(Linking l
INNER JOIN Products p
ON l.pid = c.pid)
ON c.CID = l.CID
AND p.PName = 'some product name'
WHERE l.CID IS NULL
or
SELECT c.CName, c.Cphone
FROM Customers c
WHERE NOT EXISTS(
SELECT NULL
FROM Linking l
INNER JOIN Products p
ON l.pid = c.pid
WHERE c.CID = l.CID
AND p.PName = 'some product name')
--
Jacco Schalkwijk
SQL Server MVP
"Atley" <atley_1@.hotmail.com> wrote in message
news:%23n997$C6DHA.2696@.TK2MSFTNGP09.phx.gbl...
> I have three tables:
> Products Table
> PID
> PName
> PType
> Linking Table
> LID
> PID
> CID
> Customers Table
> CID
> CName
> CPhone
>
> I am trying to make a single statement that will give me all the customers
> that haven't bought a certain single product so i can make a list of
people
> to contact regarding that product.
> Any help or suggestions would be greatly appreciated... I just can't seem
to
> get this one right.
> @.
>
>|||--The following will show records of all customers who haven't bought item
number 901.
SELECT * FROM Customers A
WHERE NOT EXISTS
(SELECT * FROM Link
WHERE CID = A.CID AND PID = 901)
--
Rohtash Kapoor
http://www.sqlmantra.com
"Atley" <atley_1@.hotmail.com> wrote in message
news:%23n997$C6DHA.2696@.TK2MSFTNGP09.phx.gbl...
> I have three tables:
> Products Table
> PID
> PName
> PType
> Linking Table
> LID
> PID
> CID
> Customers Table
> CID
> CName
> CPhone
>
> I am trying to make a single statement that will give me all the customers
> that haven't bought a certain single product so i can make a list of
people
> to contact regarding that product.
> Any help or suggestions would be greatly appreciated... I just can't seem
to
> get this one right.
> @.
>
>|||I see you have got an answer.
But please next time, do not cross-post to every newsgroup you find. This is
not a Windows Server problem, nor a datamining or datawarehouse problem.
--
Regards,
Kristofer Gafvert - IIS MVP
Reply to newsgroup only. Remove NEWS if you must reply by email, but please
do not.
www.ilopia.com - FAQ and Tutorials for Windows Server 2003
"Atley" <atley_1@.hotmail.com> wrote in message
news:%23n997$C6DHA.2696@.TK2MSFTNGP09.phx.gbl...
> I have three tables:
> Products Table
> PID
> PName
> PType
> Linking Table
> LID
> PID
> CID
> Customers Table
> CID
> CName
> CPhone
>
> I am trying to make a single statement that will give me all the customers
> that haven't bought a certain single product so i can make a list of
people
> to contact regarding that product.
> Any help or suggestions would be greatly appreciated... I just can't seem
to
> get this one right.
> @.
>
>