Question 20424 in Mysql Question posted in
The official MySQL documentation can be found here.

How to prioritize selected data based on specific column in MYSQL

I have 2 tables below:

table1

store_name store_code sub_channel
A 12345 C021
B 56789 C021
C 10000 C021

table2

product_code store_code sub_channel qty
X1 12345 10
X1 C021 20
X2 12345 C021 50
X3 56789 C021 20
X4 C021 40
X5 56789 100

I want to fetch the data from table2 based on store_code or sub_channel but only showing data only if there is the data based on store_code then select only from store_code and if it’s not then select from sub_channel.

Here is my query:

SELECT t1.store_name, t2.*
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t2.store_code = t1.store_code OR t2.sub_channel = t1.sub_channel
ORDER BY t1.store_name, t2.product_code

I got result as below:

store_name product_code store_code sub_channel qty
A X1 12345 10
A X1 C021 20
A X2 12345 C021 50
A X3 56789 C021 20
A X4 C021 40
B X1 C021 20
B X2 12345 C021 50
B X3 56789 C021 20
B X4 C021 40
B X5 56789 100
C X1 C021 20
C X2 12345 C021 50
C X3 56789 C021 20
C X4 C021 40

What I want to achieve here as below:

store_name product_code store_code sub_channel qty
A X1 12345 10
A X2 12345 C021 50
B X3 56789 C021 20
B X5 56789 100
C X1 C021 20
C X2 12345 C021 50
C X3 56789 C021 20
C X4 C021 40

Is there any way to achieve this and which additional code to add up in my query to achieve this?

Tags:

2

Answers


  1. 0

    Why not UNION ?

    SELECT ax.* FROM (
select * from table1 t1 where ifnull(store_code,'') <> ''
UNION 
select * from table1 t1 where ifnull(store_channel,'') <> '') ax 
order by ax.store_name, ax.product_code ;
    Login or Signup to reply.
  2. 0

    I think we can do this with two LEFT JOINs:

    select t1.*, 
    coalesce(t21.product_code, t22.product_code) product_code,
    coalesce(t21.qty, t22.qty) qty
from table1 t1
left join table2 t21 on t21.store_code = t1.store_code
left join table2 t22 on t22.sub_channel = t1.sub_channel and t21.store_code is null
order by store_name, product_code

    The first join attempts to match on the store code; the second join targets the sub-channel – with an additional condition that ensures that only rows that did no match on the first join are taken into account.

    Demo on DB Fiddle:

    store_name store_code sub_channel product_code qty
    A 12345 C021 X1 10
    A 12345 C021 X2 50
    B 56789 C021 X3 20
    B 56789 C021 X5 100
    C 10000 C021 X1 20
    C 10000 C021 X2 50
    C 10000 C021 X3 20
    C 10000 C021 X4 40
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search