CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlhaving
RoflcopterV RoflcopterV
RoflcopterV RoflcopterV

Reputation: 143

SQL - Select query to get purchase orders that have two or more products from different product lines

I'm wondering what I can do to rework/improve this query (it takes too long to run).

Some background info: Order ID => 'id' Purchase ID => 'pid' "Table" is a table of purchase lines, the id is the id of an order, and orders can have multiple lines. For example, there can be three lines with id 1, having various purchase ids, so:

id | pid
1 | 3
1 | 3
1 | 46

The purpose of the query is to find orders that have purchases from multiple product lines, such as 1, 21, 31, 41 and 2, 22, 32, 42.

Query below is what I came up with, but it runs very slowly from all the sub-queries, is it possible to optimize this query or get the same results with a different, faster query?

SELECT a.id
FROM Table AS a
GROUP BY a.id
HAVING (
  CAST( CASE WHEN EXISTS (SELECT NULL FROM Table WHERE pid IN ('1', '21', '31', '41') AND id = a.id ) THEN 1 ELSE 0 END AS INT) +
  CAST( CASE WHEN EXISTS (SELECT NULL FROM Table WHERE pid IN ('2', '22', '32', '42') AND id = a.id ) THEN 1 ELSE 0 END AS INT) +
  CAST( CASE WHEN EXISTS (SELECT NULL FROM Table WHERE pid IN ('3', '23', '33', '43') AND id  = a.id ) THEN 1 ELSE 0 END AS INT) +
  CAST( CASE WHEN EXISTS (SELECT NULL FROM Table WHERE pid IN ('4', '24', '34', '44') AND id = a.id ) THEN 1 ELSE 0 END AS INT) +
  CAST( CASE WHEN EXISTS (SELECT NULL FROM Table WHERE pid IN ('5', '25', '35', '45') AND id = a.id ) THEN 1 ELSE 0 END AS INT) +
  CAST( CASE WHEN EXISTS (SELECT NULL FROM Table WHERE pid IN ('6', '26', '36', '46') AND id = a.id ) THEN 1 ELSE 0 END AS INT) +
  CAST( CASE WHEN EXISTS (SELECT NULL FROM Table WHERE pid  IN ('7', '27', '37', '47') AND id = a.id ) THEN 1 ELSE 0 END AS INT) +
  CAST( CASE WHEN EXISTS (SELECT NULL FROM Table WHERE pid IN ('8', '28', '38', '48') AND id = a.id ) THEN 1 ELSE 0 END AS INT)
) > 1

EDIT:

Final working query (is 97% faster than previous):

SELECT y.Id
FROM (SELECT  x.Id,
              x.productLine
FROM ( SELECT a.id,
    CASE 
        WHEN a.pid IN ('1', '21', '31', '41') THEN 1
        WHEN a.pid IN ('2', '22', '32', '42') THEN 2
        WHEN a.pid IN ('3', '23', '33', '43') THEN 3
        WHEN a.pid IN ('4', '24', '34', '44') THEN 4
        WHEN a.pid IN ('5', '25', '35', '45') THEN 5
        WHEN a.pid IN ('6', '26', '36', '46') THEN 6
        WHEN a.pid IN ('7', '27', '37', '47') THEN 7
        WHEN a.pid IN ('8', '28', '38', '48') THEN 8
        ELSE 9 
    END AS productLine
    FROM Table AS a
    WHERE a.pid IN ('1', '21', '31', '41','2', '22', '32', '42','3', '23', '33', '43','4', '24', '34', '44','5', '25', '35', '45','6', '26', '36', '46','7', '27', '37', '47','8', '28', '38', '48')
) AS x
GROUP BY x.Id, x.productLine
) AS y
GROUP BY y.Id
HAVING COUNT(*) > 1

Upvotes: 0

Views: 2611

Answers (2)

Excelan
Excelan

Reputation: 112

Althoug I really like Rafał's solution as it gives numbers to the groups, I thought of another, little simplier solution, but was unable to test it earlier.

SELECT distinct(id) FROM store a
WHERE 4 = (
    SELECT COUNT(DISTINCT(pid)) 
    FROM store
    where (id = a.id AND (
        pid in (1, 21, 31, 41)  OR 
        pid in (2, 22, 32, 42)
        )
    )
)

Upvotes: 0

Rafał Wojtaszek
Rafał Wojtaszek

Reputation: 668

As I understand Your problem this query should meet your request:

SELECT  x.Id
FROM    ( SELECT    a.Id ,
                    CAST(a.pid AS INT) % 10 AS pid
          FROM      [Table] AS a
          GROUP BY  a.Id ,
                    CAST(a.pid AS INT) % 10
        ) x
GROUP BY x.Id
HAVING COUNT(*) > 1

After taking into account new assumptions query should look like:

SELECT y.Id
(SELECT  x.Id,
        x.pid
FROM    ( SELECT a.id,
        CASE WHEN a.pid IN ('1', '21', '31', '41') THEN 1
        WHEN a.pid IN ('2', '22', '32', '42') THEN 2
        WHEN a.pid IN ('3', '23', '33', '43') THEN 3
        WHEN a.pid IN ('4', '24', '34', '44') THEN 4
        WHEN a.pid IN ('5', '25', '35', '45') THEN 5
        WHEN a.pid IN ('6', '26', '36', '46') THEN 6
        WHEN a.pid IN ('7', '27', '37', '47') THEN 7
        WHEN a.pid IN ('8', '28', '38', '48') THEN 8
        ELSE 9 END AS productLine
        FROM Table AS a
        ) x
GROUP BY x.Id, x.pid) y
GROUP BY y.Id
HAVING COUNT(*) > 1

Upvotes: 1

Related Questions