SQL: Join two tables with different type of columns

I have two tables with following columns:

Table 1:

OrderID    RetailerName    SKUs (varchar)    OrderDate ------------------------------------------------------- 123        ABC             1,2               2016-11-11 124        DEF             3,4               2016-11-12 

Table 2:

SKU(int)          Product -------------------------- 1                 xx 2                 xy 3                 xz 4                 yx 

Expected output:

OrderID   RetailerName   OrderDate   Product --------------------------------------------   123     ABC            2016-11-11  xx   123     ABC            2016-11-11  xy   124     DEF            2016-11-12  xz   124     DEF            2016-11-12  yx 

How do I join these two tables based on SKU i.e. how do I compare SKUs (varchar) column from table1 with SKU (int) column from table2?

Asked on September 1, 2020 in Mysql.
Add Comment
2 Answer(s)

I would suggest normalizing the schema as @Sodmond suggested. However, if this is not an option, you could use find_in_set for the join condition – it will implicitly convert the int from table2 to a character:

SELECT t1.OrderID, RetailerName, OrderDate, Product FROM   table1 t1 JOIN   table2 t2 ON FIND_IN_SET(t2.sku, t1.skus) > 0 
Answered on September 1, 2020.
Add Comment

You need to redesign your schema, Check how I recreate the table for you.

table1:  OrderID      RetailerName          SKUs(int)              OrderDate 123          ABC                   1                        2016-11-11 123          ABC                   2                        2016-11-11 124          DEF                   3                        2016-11-12 124          DEF                   3                        2016-11-12   table2:  SKU(int)          Product 1                 xx 2                 xy 3                 xz 4                 yx 

Avoid storing multiple values in the SKU field, then you will be able to use the join query.

Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.