• Coriolan (8/27/2013)


    I have a requirement to find all orders that contain the same product. This same product is not defined upfront.

    declare @Orderdetail table

    (OrderID int,

    ProductID int)

    INsert into @OrderDetail Values ( 1, 100)

    INsert into @OrderDetail Values ( 1, 101)

    INsert into @OrderDetail Values ( 2, 100)

    INsert into @OrderDetail Values ( 2, 200)

    INsert into @OrderDetail Values ( 3, 300)

    In the above sample data, my expected result set would be a list of Order IDs namely( 1, 2) because Orders 1 and 2 contain product 100.

    If I have 3 new orders say Orders 10, 20, 30 that contain product 500.

    my result set will be 1,2 for product 100, and 10, 20, 30 for product 500.

    If I have a new order 100 that contains both product 100 and 500

    my result set will be 1,2,100 for product 100, 10, 20, 30 , 100 for product 500.

    Can you suggest how to approach this query?

    Thank you!

    Are you looking for all products that have been on at least two orders? Your description is not very clear.

    Maybe something like:

    select ProductID, COUNT(*)

    from @Orderdetail

    group by ProductID

    having COUNT(*) > 1

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/