union of same table in mysql

Question

I have three queries output which is from same table. but have to merge all the 3 to single mysql query

actual query:
select SKU_VARIANT1_VALCD, SKU_VARIANT1_VALUE_DESC,SKU_VARIANT2_VALCD, SKU_VARIANT2_VALUE_DESC,MIN_LOT_PRICE,MAX_LOT_PRICE
from(
select SKU_VARIANT1_VALCD, SKU_VARIANT1_VALUE_DESC, '' SKU_VARIANT2_VALCD, '' SKU_VARIANT2_VALUE_DESC,'' MIN_LOT_PRICE,'' MAX_LOT_PRICE -- null,null-- ,SKU_VARIANT2_VALCD,SKU_VARIANT2_VALUE_DESC
  FROM lot_table
  group by SKU_VARIANT1_VALCD 
 union all
 select '' SKU_VARIANT1_VALCD,'' SKU_VARIANT1_VALUE_DESC, SKU_VARIANT2_VALCD, SKU_VARIANT2_VALUE_DESC,'' MIN_LOT_PRICE,'' MAX_LOT_PRICE
  FROM lot_table
  group by SKU_VARIANT2_VALCD
 union all
 select '' SKU_VARIANT1_VALCD, '' SKU_VARIANT1_VALUE_DESC, '' SKU_VARIANT2_VALCD, '' SKU_VARIANT2_VALUE_DESC, MIN(LOT_PRICE) MIN_LOT_PRICE,  MAX(LOT_PRICE)  MAX_LOT_PRICE
  FROM lot_table
 ) a;


output getting using union (actual output):

SKU_VARIANT1_VALCD  SKU_VARIANT1_VALUE_DESC SKU_VARIANT2_VALCD  SKU_VARIANT2_VALUE_DESC MIN_LOT_PRICE   MAX_LOT_PRICE
------------------  ----------------------- ------------------  ----------------------- -------------   -------------
1   WHIITE              
12  Yellow              
15  Tan             
2   Black               
43  Olive               
5   Off White               
50  Blue                
51  Blue                
52  Blue                
56  Navy Blue               
6   GREY STONE              
70  Rust                
8   Off White               
80  Beige               
81  Pink                
85  Beige               
87  Pink                
88  Magenta             
BL  Blue                
C0002   Beige               
C0042   BLACK               
C0046   Tan             
C0092   Tan             
C0123   Brown               
OR  Gold                
PL  Metallic                
TG  Grey                
TM  Blue                
TN  Black               
TO  Navy Blue               
                                            20                  20      
                                            21                  21      
                                            23                  23      
                                            32                  32      
                                            34                  34      
                                            36                  36      
                                            37                  37      
                                            38                  38      
                                            39                  39      
                                            40                  40      
                                            41                  41      
                                            42                  42      
                                            99                  99      
                                            S0004               44      
                                            S0017               24/XXL      
                                            S0036               19      
                                            S0037               22      
                                                                                        590             6590


expected output:

SKU_VARIANT1_VALCD  SKU_VARIANT1_VALUE_DESC  SKU_VARIANT2_VALCD  SKU_VARIANT2_VALUE_DESC    MIN_LOT_PRICE   MAX_LOT_PRICE
------------------  -----------------------  ------------------  -----------------------    -------------   -------------
1                   WHIITE                  20                  20                      590             6590
12                  Yellow                  21                  21      
15                  Tan                     23                  23      
2                   Black                   32                  32      
43                  Olive                   34                  34      
5                   Off White               36                  36      
50                  Blue                    37                  37      
51                  Blue                    38                  38      
52                  Blue                    39                  39      
56                  Navy Blue               40                  40      
6                   GREY STONE              41                  41      
70                  Rust                    42                  42      
8                   Off White               99                  99      
80                  Beige                   S0004               44      
81                  Pink                    S0017               24/XXL      
85                  Beige                   S0036               19      
87                  Pink                    S0037               22      
88                  Magenta             
BL                  Blue                
C0002               Beige               
C0042               BLACK               
C0046               Tan             
C0092               Tan             
C0123               Brown               
OR                  Gold                
PL                  Metallic                
TG                  Grey                
TM                  Blue                
TN                  Black               
TO                  Navy Blue

all the three output are the distinct values there is not relation to one another. i just need to put it parallel to one another. The actual output i tried using union with the 3 quires but didn't the result which is expected.

thanks in advance,,,,

Relation is established with a JOIN clause in your FROM clause, not by stacking results in a union. Can you share some sample data from the table and your desired results for that sample data and we can help out more (I'm guessing this is a self-join type of thing, but it's hard to say). — JNevill, Aug 21 '20 at 17:39
@JNevill thanks for the reply. AS of now these are the data i have. — Benny, Aug 21 '20 at 17:50
Does SKU_VARIANT1_VALCD always have the number of rows >= from another table? and whether query speed is one of your considerations? i have some idea for alternative but i affraid it will be slow and depends on the number of rows of data in SKU_VARIANT1_VALCD — Ronny Sulistio, Aug 24 '20 at 06:26

FanoFN · Answer 1 · 2020-08-27T02:37:53.433

You can assign ROW_NUMBER() on each query and JOIN using it if it just for the sake of showing them side by side. Something like this query below:

SELECT SKU_VARIANT1_VALCD, SKU_VARIANT1_VALUE_DESC,
       SKU_VARIANT2_VALCD, SKU_VARIANT2_VALUE_DESC,
       MIN_LOT_PRICE, MAX_LOT_PRICE 
 FROM
(SELECT SKU_VARIANT1_VALCD, SKU_VARIANT1_VALUE_DESC, ROW_NUMBER() OVER (ORDER BY SKU_VARIANT1_VALCD) rnum
FROM lot_table WHERE SKU_VARIANT1_VALCD IS NOT NULL
GROUP BY SKU_VARIANT1_VALCD) A 
LEFT JOIN
(SELECT SKU_VARIANT2_VALCD, SKU_VARIANT2_VALUE_DESC, ROW_NUMBER() OVER (ORDER BY SKU_VARIANT2_VALCD) rnum
FROM lot_table WHERE SKU_VARIANT2_VALCD IS NOT NULL
GROUP BY SKU_VARIANT2_VALCD) B ON A.rnum=B.rnum
LEFT JOIN
(SELECT MIN(LOT_PRICE) MIN_LOT_PRICE,  MAX(LOT_PRICE)  MAX_LOT_PRICE, ROW_NUMBER() OVER (ORDER BY SKU_VARIANT2_VALCD) rnum
 FROM lot_table) C ON A.rnum=C.rnum;

Along with this fiddle demo example .

score 0 · Answer 2 · answered Aug 25 '20 at 07:53

Could you provide the table structure of your lot_table?

I am guessing your lot_table has the following structure:

SKU_VARIANT1_VALCD, 
SKU_VARIANT1_VALUE_DESC,
SKU_VARIANT2_VALCD, 
SKU_VARIANT2_VALUE_DESC,
LOT_PRICE

I am making this inference from your actual query you have posted.

It looks like you want to find the minimum lot price and maximum lot price for the variant1 and variant2 combinations. Is this correct?

If yes, why would you even require a union-all? Shouldn't a simple group-by query work for you? Here's what I would do if the table structure I mentioned above is correct.

select 
  SKU_VARIANT1_VALCD, 
  SKU_VARIANT1_VALUE_DESC, 
  SKU_VARIANT2_VALCD, 
  SKU_VARIANT2_VALUE_DESC, 
  MIN(LOT_PRICE) MIN_LOT_PRICE,
  MAX(LOT_PRICE) MAX_LOT_PRICE
from lot_table
group by SKU_VARIANT1_VALCD, 
  SKU_VARIANT1_VALUE_DESC, 
  SKU_VARIANT2_VALCD, 
  SKU_VARIANT2_VALUE_DESC;

union of same table in mysql

2 Answers2