Home SQL improvement - UNION?
Reply: 0

SQL improvement - UNION?

user2703
1#
user2703 Published in June 22, 2018, 11:32 am

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO