Kinda Weird query....help!

[tortelvis]

I have a table with three vars.
Customer_Order, Item_ID, Seller
I need to make a query that pulls only the customer_orders where seller has multiple entries per customer_orders.

Something like this....

Customer_Order Item_ID Seller
10001 xyz 121212
10001 fgh 121212
10001 jed 343434
10002 fgg 121212
10002 xyz 121212
10003 jed 343434
10003 hje 343434

The query would return:
Customer_Order Item_ID Seller
10001 xyz 121212
10001 fgh 121212
10001 jed 343434

Because thats the only place where there are multiple sellers for a specific customer_order. I need it to return all 3 fields though.

Thanks,

Tort

 

[jebry]

Hi Tort!

You can do this using two queries:

Query1

Select Count(ItemID) As NumberOfEntries, Customer_Order, Seller From YourTable Group By Customer_Order, Seller

Query2

Select YourTable.Customer_Order, ItemID, YourTable.Seller From YourTable Inner Join Query1 On YourTable.Customer_Order = Query1.CustomerOrder And YourTable.Seller = Query1.Seller Where NumberOfEntries > 1

hth
Jeff Bridgham
bridgham@purdue.edu

 

[mwolf00]

I think this will work:


SELECT c.customer_order, c.item_ID, c.seller FROM
myTable c JOIN
[SELECT customer_order, count(seller) from myTable HAVING count(seller) > 1] as mult ON c.customer_order = mult.customer_order -----------------------------------------------------------------
"Whether you think that you can, or that you can't, you are usually right."
- Henry Ford (1863-1947)

mikewolf@tst-us.com