|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Simple query?I have a simpel select query that is selecting customer records. SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE CustomerNumber IN ('1', '2', '3') Sample result: Cust # Item# 1 100 1 101 1 102 1 104 2 100 2 102 2 103 How can I make the query return only the common item numbers between customer numbers? Cust # Item# 1 100 1 102 2 100 2 102 Thanks...Tom Try this
SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE CustomerNumber IN ('1', '2', '3') AND ItemNumber IN (100, 102) Show quote "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message news:uT4cp$RxFHA.2312@TK2MSFTNGP14.phx.gbl... > This seems like it should be simple... > > I have a simpel select query that is selecting customer records. > SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE > CustomerNumber IN ('1', '2', '3') > > Sample result: > > Cust # Item# > 1 100 > 1 101 > 1 102 > 1 104 > 2 100 > 2 102 > 2 103 > > How can I make the query return only the common item numbers between > customer numbers? > Cust # Item# > 1 100 > 1 102 > 2 100 > 2 102 > > Thanks...Tom > > The problem with that is we don't know the exact item numbers ordered
between customers. The results I am getting back number in excess of 2000 rows. The actual number of items common to 2 given customers is probably around 100 Show quote "Yosh" <yoshi@nospam.com> wrote in message news:uajwqCSxFHA.2792@tk2msftngp13.phx.gbl... > Try this > > SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE > CustomerNumber IN ('1', '2', '3') AND ItemNumber IN (100, 102) > > > "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message > news:uT4cp$RxFHA.2312@TK2MSFTNGP14.phx.gbl... > > This seems like it should be simple... > > > > I have a simpel select query that is selecting customer records. > > SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE > > CustomerNumber IN ('1', '2', '3') > > > > Sample result: > > > > Cust # Item# > > 1 100 > > 1 101 > > 1 102 > > 1 104 > > 2 100 > > 2 102 > > 2 103 > > > > How can I make the query return only the common item numbers between > > customer numbers? > > Cust # Item# > > 1 100 > > 1 102 > > 2 100 > > 2 102 > > > > Thanks...Tom > > > > > > Tom,
So you only want a record if ALL customers i.e., 3, 4, 5 etc...incl (1 and 2) have purchased the same item? Show quote "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message news:%23bkGDGSxFHA.1856@TK2MSFTNGP12.phx.gbl... > The problem with that is we don't know the exact item numbers ordered > between customers. The results I am getting back number in excess of 2000 > rows. The actual number of items common to 2 given customers is probably > around 100 > > "Yosh" <yoshi@nospam.com> wrote in message > news:uajwqCSxFHA.2792@tk2msftngp13.phx.gbl... >> Try this >> >> SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE >> CustomerNumber IN ('1', '2', '3') AND ItemNumber IN (100, 102) >> >> >> "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message >> news:uT4cp$RxFHA.2312@TK2MSFTNGP14.phx.gbl... >> > This seems like it should be simple... >> > >> > I have a simpel select query that is selecting customer records. >> > SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE >> > CustomerNumber IN ('1', '2', '3') >> > >> > Sample result: >> > >> > Cust # Item# >> > 1 100 >> > 1 101 >> > 1 102 >> > 1 104 >> > 2 100 >> > 2 102 >> > 2 103 >> > >> > How can I make the query return only the common item numbers between >> > customer numbers? >> > Cust # Item# >> > 1 100 >> > 1 102 >> > 2 100 >> > 2 102 >> > >> > Thanks...Tom >> > >> > >> >> > > Yes. Generally the customer numbers will be supplied as in the example I
gave. In most cases it would be a comparison between 2 customers but it could extend to more than 2... Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:%23VtxsJSxFHA.3864@TK2MSFTNGP12.phx.gbl... > Tom, > > So you only want a record if ALL customers i.e., 3, 4, 5 etc...incl (1 and > 2) have purchased the same item? > > "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message > news:%23bkGDGSxFHA.1856@TK2MSFTNGP12.phx.gbl... > > The problem with that is we don't know the exact item numbers ordered > > between customers. The results I am getting back number in excess of 2000 > > rows. The actual number of items common to 2 given customers is probably > > around 100 > > > > "Yosh" <yoshi@nospam.com> wrote in message > > news:uajwqCSxFHA.2792@tk2msftngp13.phx.gbl... > >> Try this > >> > >> SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE > >> CustomerNumber IN ('1', '2', '3') AND ItemNumber IN (100, 102) > >> > >> > >> "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message > >> news:uT4cp$RxFHA.2312@TK2MSFTNGP14.phx.gbl... > >> > This seems like it should be simple... > >> > > >> > I have a simpel select query that is selecting customer records. > >> > SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE > >> > CustomerNumber IN ('1', '2', '3') > >> > > >> > Sample result: > >> > > >> > Cust # Item# > >> > 1 100 > >> > 1 101 > >> > 1 102 > >> > 1 104 > >> > 2 100 > >> > 2 102 > >> > 2 103 > >> > > >> > How can I make the query return only the common item numbers between > >> > customer numbers? > >> > Cust # Item# > >> > 1 100 > >> > 1 102 > >> > 2 100 > >> > 2 102 > >> > > >> > Thanks...Tom > >> > > >> > > >> > >> > > > > > > Tom,
Do the records need to be returned a seperate record for each customer/item or can you accept just a distinct list of item numbers in the resultset based on the customerid's submitted? HTH Jerry Show quote "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message news:%2363u2PSxFHA.736@tk2msftngp13.phx.gbl... > Yes. Generally the customer numbers will be supplied as in the example I > gave. In most cases it would be a comparison between 2 customers but it > could extend to more than 2... > > "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message > news:%23VtxsJSxFHA.3864@TK2MSFTNGP12.phx.gbl... >> Tom, >> >> So you only want a record if ALL customers i.e., 3, 4, 5 etc...incl (1 >> and >> 2) have purchased the same item? >> >> "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message >> news:%23bkGDGSxFHA.1856@TK2MSFTNGP12.phx.gbl... >> > The problem with that is we don't know the exact item numbers ordered >> > between customers. The results I am getting back number in excess of > 2000 >> > rows. The actual number of items common to 2 given customers is > probably >> > around 100 >> > >> > "Yosh" <yoshi@nospam.com> wrote in message >> > news:uajwqCSxFHA.2792@tk2msftngp13.phx.gbl... >> >> Try this >> >> >> >> SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE >> >> CustomerNumber IN ('1', '2', '3') AND ItemNumber IN (100, 102) >> >> >> >> >> >> "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message >> >> news:uT4cp$RxFHA.2312@TK2MSFTNGP14.phx.gbl... >> >> > This seems like it should be simple... >> >> > >> >> > I have a simpel select query that is selecting customer records. >> >> > SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE >> >> > CustomerNumber IN ('1', '2', '3') >> >> > >> >> > Sample result: >> >> > >> >> > Cust # Item# >> >> > 1 100 >> >> > 1 101 >> >> > 1 102 >> >> > 1 104 >> >> > 2 100 >> >> > 2 102 >> >> > 2 103 >> >> > >> >> > How can I make the query return only the common item numbers between >> >> > customer numbers? >> >> > Cust # Item# >> >> > 1 100 >> >> > 1 102 >> >> > 2 100 >> >> > 2 102 >> >> > >> >> > Thanks...Tom >> >> > >> >> > >> >> >> >> >> > >> > >> >> > > if the combination (CustomerNumber , ItemNumber ) is unique
select ItemNumber, count(*) from SomeTable where CustomerNumber in(...) group by ItemNumber having count(*)=(select count(distinct CustomerNumber) from SomeTable where CustomerNumber in(...)) It would require separate records. There will usually be quantities and
dollars requested as well. SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] Sum(QuantityOrdered) AS Quantity, Sum(DollarsOrdered) AS Dollars WHERE CustomerNumber IN ('1', '2', '3') GROUP BY CustomerNumber, ItemNumber So results like this would be likely: Cust # Item# Quantity Dollars 1 100 10 2343.00 1 102 1 100.00 2 100 5 1171.50 2 102 1 100.00 Show quote "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message news:OofFkTSxFHA.2652@TK2MSFTNGP14.phx.gbl... > Tom, > > Do the records need to be returned a seperate record for each customer/item > or can you accept just a distinct list of item numbers in the resultset > based on the customerid's submitted? > > HTH > > Jerry > "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message > news:%2363u2PSxFHA.736@tk2msftngp13.phx.gbl... > > Yes. Generally the customer numbers will be supplied as in the example I > > gave. In most cases it would be a comparison between 2 customers but it > > could extend to more than 2... > > > > "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message > > news:%23VtxsJSxFHA.3864@TK2MSFTNGP12.phx.gbl... > >> Tom, > >> > >> So you only want a record if ALL customers i.e., 3, 4, 5 etc...incl (1 > >> and > >> 2) have purchased the same item? > >> > >> "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message > >> news:%23bkGDGSxFHA.1856@TK2MSFTNGP12.phx.gbl... > >> > The problem with that is we don't know the exact item numbers ordered > >> > between customers. The results I am getting back number in excess of > > 2000 > >> > rows. The actual number of items common to 2 given customers is > > probably > >> > around 100 > >> > > >> > "Yosh" <yoshi@nospam.com> wrote in message > >> > news:uajwqCSxFHA.2792@tk2msftngp13.phx.gbl... > >> >> Try this > >> >> > >> >> SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE > >> >> CustomerNumber IN ('1', '2', '3') AND ItemNumber IN (100, 102) > >> >> > >> >> > >> >> "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message > >> >> news:uT4cp$RxFHA.2312@TK2MSFTNGP14.phx.gbl... > >> >> > This seems like it should be simple... > >> >> > > >> >> > I have a simpel select query that is selecting customer records. > >> >> > SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE > >> >> > CustomerNumber IN ('1', '2', '3') > >> >> > > >> >> > Sample result: > >> >> > > >> >> > Cust # Item# > >> >> > 1 100 > >> >> > 1 101 > >> >> > 1 102 > >> >> > 1 104 > >> >> > 2 100 > >> >> > 2 102 > >> >> > 2 103 > >> >> > > >> >> > How can I make the query return only the common item numbers between > >> >> > customer numbers? > >> >> > Cust # Item# > >> >> > 1 100 > >> >> > 1 102 > >> >> > 2 100 > >> >> > 2 102 > >> >> > > >> >> > Thanks...Tom > >> >> > > >> >> > > >> >> > >> >> > >> > > >> > > >> > >> > > > > > > Tom,
Try something like this (only written for two customerids currently): CREATE PROC USP_CUSTPROC @CUST1 INT, @CUST2 INT AS SELECT CUSTOMERID, ITEMID, QUANTITY, DOLLARS FROM TCUSTOMERS WHERE ITEMID IN (SELECT ITEMID FROM TCUSTOMERS WHERE CUSTOMERID = @CUST1 AND ITEMID IN (SELECT ITEMID FROM TCUSTOMERS WHERE CUSTOMERID = @CUST2)) HTH Jerry Show quote "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message news:%23YCIKZSxFHA.3180@TK2MSFTNGP14.phx.gbl... > It would require separate records. There will usually be quantities and > dollars requested as well. > > SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] > Sum(QuantityOrdered) AS Quantity, Sum(DollarsOrdered) AS Dollars WHERE > CustomerNumber IN ('1', '2', '3') GROUP BY CustomerNumber, ItemNumber > > So results like this would be likely: > > Cust # Item# Quantity Dollars > 1 100 10 2343.00 > 1 102 1 100.00 > 2 100 5 1171.50 > 2 102 1 100.00 > > > "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message > news:OofFkTSxFHA.2652@TK2MSFTNGP14.phx.gbl... >> Tom, >> >> Do the records need to be returned a seperate record for each > customer/item >> or can you accept just a distinct list of item numbers in the resultset >> based on the customerid's submitted? >> >> HTH >> >> Jerry >> "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message >> news:%2363u2PSxFHA.736@tk2msftngp13.phx.gbl... >> > Yes. Generally the customer numbers will be supplied as in the example > I >> > gave. In most cases it would be a comparison between 2 customers but >> > it >> > could extend to more than 2... >> > >> > "Jerry Spivey" <jspi***@vestas-awt.com> wrote in message >> > news:%23VtxsJSxFHA.3864@TK2MSFTNGP12.phx.gbl... >> >> Tom, >> >> >> >> So you only want a record if ALL customers i.e., 3, 4, 5 etc...incl (1 >> >> and >> >> 2) have purchased the same item? >> >> >> >> "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message >> >> news:%23bkGDGSxFHA.1856@TK2MSFTNGP12.phx.gbl... >> >> > The problem with that is we don't know the exact item numbers >> >> > ordered >> >> > between customers. The results I am getting back number in excess >> >> > of >> > 2000 >> >> > rows. The actual number of items common to 2 given customers is >> > probably >> >> > around 100 >> >> > >> >> > "Yosh" <yoshi@nospam.com> wrote in message >> >> > news:uajwqCSxFHA.2792@tk2msftngp13.phx.gbl... >> >> >> Try this >> >> >> >> >> >> SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE >> >> >> CustomerNumber IN ('1', '2', '3') AND ItemNumber IN (100, 102) >> >> >> >> >> >> >> >> >> "Tom Gaughan" <tgaughan@no2spam.numail.org> wrote in message >> >> >> news:uT4cp$RxFHA.2312@TK2MSFTNGP14.phx.gbl... >> >> >> > This seems like it should be simple... >> >> >> > >> >> >> > I have a simpel select query that is selecting customer records. >> >> >> > SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE >> >> >> > CustomerNumber IN ('1', '2', '3') >> >> >> > >> >> >> > Sample result: >> >> >> > >> >> >> > Cust # Item# >> >> >> > 1 100 >> >> >> > 1 101 >> >> >> > 1 102 >> >> >> > 1 104 >> >> >> > 2 100 >> >> >> > 2 102 >> >> >> > 2 103 >> >> >> > >> >> >> > How can I make the query return only the common item numbers > between >> >> >> > customer numbers? >> >> >> > Cust # Item# >> >> >> > 1 100 >> >> >> > 1 102 >> >> >> > 2 100 >> >> >> > 2 102 >> >> >> > >> >> >> > Thanks...Tom >> >> >> > >> >> >> > >> >> >> >> >> >> >> >> > >> >> > >> >> >> >> >> > >> > >> >> > > Look up "relational division" in this newsgroup and/or Google.
If you post more DDL and sample data we can help you design a solution. Some time ago I posted a function that pretty much does what you need in the context of the "pubs" database. ML On Thu, 29 Sep 2005 14:02:08 -0400, Tom Gaughan wrote:
Show quote >This seems like it should be simple... Hi Tom,> >I have a simpel select query that is selecting customer records. >SELECT CustomerNumber as [Cust #], ItemNumber as [Item #] WHERE >CustomerNumber IN ('1', '2', '3') > >Sample result: > >Cust # Item# >1 100 >1 101 >1 102 >1 104 >2 100 >2 102 >2 103 > >How can I make the query return only the common item numbers between >customer numbers? >Cust # Item# >1 100 >1 102 >2 100 >2 102 > >Thanks...Tom > This one will return items ordered by two customers, but can't be easily expanded to three, four, ... customers: SELECT a.CustomerNumber, a.ItemNumber, a.OtherColumns FROM YourTable AS a INNER JOIN YourTable AS b ON b.ItemNumber = a.ItemNumber AND b.CustomerNumber <> a.CustomerNumber -- The lines below are optional, if you want to limit the search WHERE a.CustomerNumber IN ('1', '2', '3', ...) AND b.CustomerNumber IN ('1', '2', '3', ...) The next query can be expanded to search for items ordered by three, four, ... customers. But you can't return other data than just the item numbers: SELECT ItemNumber FROM YourTable -- The WHERE clause is optional, if you want to limit the search WHERE CustomerNumber IN ('1', '2', '3', ...) -- But the GROUP BY and HAVING are necessary GROUP BY ItemNumber HAVING COUNT(*) >= 3 The third query uses the second as a derived table to show more columns: SELECT a.CustomerNumber, a.ItemNumber, a.OtherColumns FROM YourTable AS a INNER JOIN (SELECT ItemNumber FROM YourTable WHERE CustomerNumber IN ('1', '2', '3', ...) GROUP BY ItemNumber HAVING COUNT(*) >= 3) AS b ON b.ItemNumber = a.ItemNumber WHERE a.CustomerNumber IN ('1', '2', '3', ...) If you want to limit the search to some customers, you have to include the requirement twice - both in the derived table and in the outer query, as shown above. If you include it in only one of these places, you'll get wrong results. Note: All the above queries are untested. See www.aspfaq.com/5006 if you prefer a tested solution. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics