Home SQL improvement - UNION?
Reply: 3

SQL improvement - UNION?

adlisval
1#
adlisval Published in 2017-11-14 14:28:48Z

I have 2 tables: "packages" and "items".

"packages" has the following columns: pack_id | item_id

"items" has the following columns.......: item_id | type

One package can have multiple items.

UPDATE:

Sample Data and Expected Results:

pack_id | item_id
1           1
1           2
1           3
2           2
2           1
3           4
3           5
3           6

item_id | type
1           C
2           F
3           Z
4           Z
5           Z
6           Z

Expected result:

pack_id | pack_type
1           Mixed
2           Cool
3           Not Cool

My question is:

Is there a better way to query these 2 tables, in order to get a 3rd one, having as columns: "pack_id" and the new column "pack_type"? Those UNIONs seem to add a huge amount of time to the run time.

    SELECT pack_id, pack_type
    FROM (    
      -- CASE 1 - pack_type is 'Cool' - type 'C' or 'F', but not 'Z'        
      SELECT  Distinct q1.pack_id, 'Cool' AS pack_type    
        FROM (
            SELECT  Distinct p.pack_id, i.type
            FROM packages p 
            INNER JOIN items i
                ON p.item_id = i.item_id
            WHERE i.type <> 'Z') q1
        WHERE q1.type IN ('C','F')

      UNION        

      -- CASE 2 - pack_type is 'Not-Cool' - type 'Z', but not 'C' or 'F'
      SELECT  Distinct q2.pack_id, 'Not-Cool' AS pack_type    
        FROM (
            SELECT  Distinct p.pack_id, i.type
            FROM packages p 
            INNER JOIN items i
                ON p.item_id = i.item_id
            WHERE i.type = 'Z') q2
        WHERE q2.type NOT IN ('C','F')

      UNION

     -- CASE 3 - pack_type is 'Mixed' - type 'Z' and ('C' or 'F')
     SELECT  Distinct q3.pack_id, 'Mixed' AS pack_type    
        FROM (
            SELECT  Distinct p.pack_id, i.type
            FROM packages p 
            INNER JOIN items i
                ON p.item_id = i.item_id
            WHERE i.type = 'Z') q3
        WHERE q3.type IN ('C','F')
    ) m

NOTE: some explication for the multiple comments that point to "A type cannot be Z and also C or F".

I also thought that, but the way I understand the behavior of the query is that if u say "type IN ('C','F')", it will look at each item of type 'C' or 'F' and get the package id that has that item. BUT, a package that has items of type 'C' or 'F' can also have items of type 'Z', hence the inner select, dealing with items of type 'Z' first.

Juan Carlos Oropeza
2#
Juan Carlos Oropeza Reply to 2017-11-21 12:58:17Z

Even when isnt explain on the question I guess your package type is derived from the combination of items on it. So you need group the items on each package

SQL DEMO

WITH cte as (
     SELECT pack_id, 
            COUNT(CASE WHEN i.type IN ('C','F') THEN 1 END) as total_CF,
            COUNT(CASE WHEN i.type = 'Z' THEN 1 END) as total_Z
     FROM packages p 
     INNER JOIN items i 
        ON p.item_id = i.item_id
     GROUP BY pack_id 
)
SELECT pack_id, 
       CASE WHEN total_CF > 0 and total_Z = 0 THEN 'Cool'
            WHEN total_CF = 0 and total_Z > 0 THEN 'Not Cool'
            WHEN total_CF > 0 and total_Z > 0 THEN 'Mixed'                
         -- ELSE 'NOT determinated'
       END as type
FROM cte

OUTPUT

Edward N
3#
Edward N Reply to 2017-11-14 14:54:49Z

I think you can put it in single query like this

SELECT  Distinct * 
FROM (
SELECT  p.pack_id,
        CASE WHEN i.type <> 'Z' AND i.type IN ('C','F') THEN 'Cool'
             WHEN i.type = 'Z' AND i.type NOT IN ('C','F') THEN 'Not-Cool'
             WHEN i.type = 'Z' AND i.type IN ('C','F') THEN 'Mixed'
        END AS pack_type
FROM packages p 
INNER JOIN items i ON p.item_id = i.item_id
WHERE (i.type <> 'Z' AND i.type IN ('C','F'))
    OR (i.type = 'Z' AND i.type NOT IN ('C','F'))
    OR (i.type = 'Z' AND i.type IN ('C','F'))
) as temp

Updated: After reviewed carefully your logic, I think @Tim answer is the right one

Juan Carlos Oropeza
4#
Juan Carlos Oropeza Reply to 2017-11-14 14:44:37Z

Maybe i have misunderstood something, but isn't this what you actually want:

SELECT DISTINCT 
    pack_id, 
    pack_type = CASE WHEN i.type IN ('C','F') THEN 'Cool'
                     WHEN i.type IN ('Z') THEN 'Not-Cool'
                     ELSE 'Mixed' END
FROM  packages p INNER JOIN items i ON p.item_id = i.item_id

There are three types: cool or not cool and everything else and you don't want a filter(just unique).

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.356213 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO