AND and One to many relationships

I have two tables in a one to many relationship, customers and invoices. The
invoices can be a variety of types marked by an invoice flag column (for
instance 1, 2 or 3). I want to retrieve customers, which only have invoices
of 1 AND 2. If there is an invoice of type 3 I don't want the customer
returned. How do I do this SQL
SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>I have two tables in a one to many relationship, customers and invoices.
>The invoices can be a variety of types marked by an invoice flag column
>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>customer returned. How do I do this SQL
>
|||I can't get this to work. If the customer has invoices of type 1 and 2 I
want the customer returned. If i.InvoiceType IN (1,2) in the query and
customer has an invoice type of 3 I don't want the customer returned.
Does this make sense. Thanks.
SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>
|||SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
AND NOT EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType = 3)
?
That gives you customers that have invoice with type either 1 or 2 but don't
have invoices of type 3.
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:u8eMEMjKFHA.2852@.TK2MSFTNGP14.phx.gbl...
>I can't get this to work. If the customer has invoices of type 1 and 2 I
>want the customer returned. If i.InvoiceType IN (1,2) in the query and
>customer has an invoice type of 3 I don't want the customer returned.
> Does this make sense. Thanks.
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> wrote in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
|||select c.*
from customers c
where c.customerid not in (select customerid from invoices where
invoicetype = 3)
if you need other data from the invoice table then just do a join to
it.
i am presuming in this example that customers can have all three types,
but will have the same customerid on each row in the invoice table,
thus if you exclude the customers on an id level, then it will exclude
the ones that have any combination of 3 (1-2-3, 1-3, 2-3, 3)
hth,
hans
|||Hi
As I have understood you need the list of customers who have invoices of
type 1 AND 2 so we can't use IN keyword because it is actually a type of OR.
Here is the query (I assumed invoice Type field is char):
select distinct A.idcustomer from
(select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
(select idinvoice,idcustomer from tblinvoice where type='2') B on
A.idcustomer=B.idcustomer
and not exists (select 'true' from tblinvoice C where
C.idcustomer=A.idcustomer and C.type not in ('1','2'))
|||Hi this is going in the right direction. To complicate things would there be
any way to make the query more dynamic. Say there were 5 invoices types and
I need to show customers with invoices of invoice type 1 AND 3 but then
wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
"Reza" <Reza@.discussions.microsoft.com> wrote in message
news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
> Hi
> As I have understood you need the list of customers who have invoices of
> type 1 AND 2 so we can't use IN keyword because it is actually a type of
> OR.
> Here is the query (I assumed invoice Type field is char):
> select distinct A.idcustomer from
> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
> (select idinvoice,idcustomer from tblinvoice where type='2') B on
> A.idcustomer=B.idcustomer
> and not exists (select 'true' from tblinvoice C where
> C.idcustomer=A.idcustomer and C.type not in ('1','2'))
|||On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:

>Hi this is going in the right direction. To complicate things would there be
>any way to make the query more dynamic. Say there were 5 invoices types and
>I need to show customers with invoices of invoice type 1 AND 3 but then
>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
Hi Chris,
For full flexibility, create an extra table to hold the types you need.
Then, to find all customers with all requested types of invoice plus
possibly others, use
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
INNER JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
And to find all customers with all requested types of invoice, but no
others, you change this to
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
LEFT JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
AND COUNT(*) = COUNT(t.Type)
Both above queries are untested. Post CREATE TABLE and INSERT statements
with test data if you want a tested solution.

>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Where does the Types wanted table come from. Is this like a many to many
relationship with customers having many invoices and invoices types having
many invoices?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2j4j31lmk3p78au3eookj29outfd7uaei2@.4ax.com...
> On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>
> Hi Chris,
> For full flexibility, create an extra table to hold the types you need.
> Then, to find all customers with all requested types of invoice plus
> possibly others, use
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> INNER JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> And to find all customers with all requested types of invoice, but no
> others, you change this to
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> LEFT JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> AND COUNT(*) = COUNT(t.Type)
> Both above queries are untested. Post CREATE TABLE and INSERT statements
> with test data if you want a tested solution.
>
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:

>Where does the Types wanted table come from. Is this like a many to many
>relationship with customers having many invoices and invoices types having
>many invoices?
Hi Chris,
The TypesWanted table is where you (temporarily) store the types you are
looking for. Your question was:[vbcol=seagreen]
To find customer with types 1 and 3 and 5, just run
DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
INSERT TypesWanted (Type) VALUE(1);
INSERT TypesWanted (Type) VALUE(3);
INSERT TypesWanted (Type) VALUE(5);
And then run the query.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)