I have a matrix table set up that returns some invoice amounts for each
customer, by month. The months are going across the top; the customers going
down the side; amounts as the 'data' portion.
Anyway, in my actual database table, some customers didn't have invoice
amounts for certain months (ie. they didn't buy products for that
month).When I run a query by month, only the months that they have invoices
for will show up.
Ex.
Cust Mnt InvAmt
1 1 $5
1 2 $5
1 9 $5
2 12 $0
Now, when I run my matrix table, all works great except that there are nulls
in the spots under the months no invoices exist (for instance, months 1 -11
for cust#2 in my example would show nulls, but month 12 would show $0).
I set up an IIF statement in my stored procedure (which is the dataset for
my matrix table) that will replace nulls with 0s, but since there are no
rows for the months with no invoices, the nulls don't exist and, therefore,
0s aren't inserted.
I've also tried making an IIF statement right inside the Expression part of
the matrix cell but I keep getting compilation errors.
Any ideas? TIA!I usually resolve this sort of problem in one of two ways, either use an
OUTER JOIN or UNION in the underlying query to make sure you get at least
one row for every month. Combined with the ISNULL function to make sure
there are zero values in the appropriate columns. This gives the matrix what
it needs to produce the sort of result I think you are looking for.
Sometimes it means you have to have a contrived table to enable this, I
usually have something like a Period table which acts as a time dimension for
just this sort of thing.
Hope this helps
Mike
"Kelly" wrote:
> I have a matrix table set up that returns some invoice amounts for each
> customer, by month. The months are going across the top; the customers going
> down the side; amounts as the 'data' portion.
> Anyway, in my actual database table, some customers didn't have invoice
> amounts for certain months (ie. they didn't buy products for that
> month).When I run a query by month, only the months that they have invoices
> for will show up.
> Ex.
> Cust Mnt InvAmt
> 1 1 $5
> 1 2 $5
> 1 9 $5
> 2 12 $0
> Now, when I run my matrix table, all works great except that there are nulls
> in the spots under the months no invoices exist (for instance, months 1 -11
> for cust#2 in my example would show nulls, but month 12 would show $0).
> I set up an IIF statement in my stored procedure (which is the dataset for
> my matrix table) that will replace nulls with 0s, but since there are no
> rows for the months with no invoices, the nulls don't exist and, therefore,
> 0s aren't inserted.
> I've also tried making an IIF statement right inside the Expression part of
> the matrix cell but I keep getting compilation errors.
> Any ideas? TIA!
>
>
Showing posts with label invoice. Show all posts
Showing posts with label invoice. Show all posts
Thursday, March 8, 2012
Friday, February 24, 2012
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)
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)
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 SQLSELECT <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 b
e
>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)
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 SQLSELECT <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 b
e
>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)
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 SQLSELECT <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...
>>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))
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 <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
>>
>|||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...
>> 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'))
>
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 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...
>> 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'))
>
> 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:
>>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.
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)|||Works great. Cheers.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e23k311ik0teriodjc3ik2u6jie07kh8j4@.4ax.com...
> 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:
>>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.
> 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)
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 SQLSELECT <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...
>>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))
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 <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
>>
>|||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...
>> 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'))
>
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 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...
>> 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'))
>
> 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:
>>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.
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)|||Works great. Cheers.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e23k311ik0teriodjc3ik2u6jie07kh8j4@.4ax.com...
> 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:
>>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.
> 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)
Subscribe to:
Posts (Atom)