Join tables when one column has a prefix

Question

This is my "product" table WHERE sku RLIKE 'IP-205-1067-16' or sku IN ('205-d-SC55G','205-d-RC099G','205-d-SC45G','205-d-RC099G'):

+---------------------------+---------+-----------------------+-----------------------+-------------------------+----------------------------+
|            sku            | price_1 | full_color_imprint_id | embroidery_imprint_id | screen_print_imprint_id | laser_engraving_imprint_id |
+---------------------------+---------+-----------------------+-----------------------+-------------------------+----------------------------+
| IP-205-1067-16            | 1.55556 | NULL                  | NULL                  | 63276                   | 32539                      |
| IP-205-1067-16-39423495   | 1.55556 | NULL                  | NULL                  | 63276                   | 32539                      |
| IP-205-1067-16-1272347    | 1.55556 | NULL                  | NULL                  | 63276                   | 32539                      |
| IP-205-1067-16-56185      | 1.55556 | NULL                  | NULL                  | 63276                   | 32539                      |
| IP-205-1067-16-1706399150 | 1.55556 | NULL                  | NULL                  | 63276                   | 32539                      |
| 205-d-SC45G               | 45      | NULL                  | NULL                  | NULL                    | NULL                       |
| 205-d-SC55G               | 55      | NULL                  | NULL                  | NULL                    | NULL                       |
| 205-d-RC059G              | 0.59    | NULL                  | NULL                  | NULL                    | NULL                       |
| 205-d-RC099G              | 0.99    | NULL                  | NULL                  | NULL                    | NULL                       |
+---------------------------+---------+-----------------------+-----------------------+-------------------------+----------------------------+

This is my "imprint_locations" table WHERE imprint_method_id IN (63276,32539):

+--------+-------------------+------------+-------------+--------------+
|   id   | imprint_method_id |    name    |  setup_sku  | running_sku  |
+--------+-------------------+------------+-------------+--------------+
| 809128 |             32539 | Body Right | 205-d-SC45G | 205-d-RC059G |
| 809129 |             32539 | Body Left  | 205-d-SC45G | 205-d-RC059G |
| 808288 |             63276 | Body Right | 205-d-SC55G | 205-d-RC099G |
| 808289 |             63276 | Body Left  | 205-d-SC55G | 205-d-RC099G
+--------+-------------------+------------+-------------+--------------+

This is the end result I'm trying to achieve:

+---------------------------+-----------------------------------------+-------------------------------------------+-------------------------------------------+---------------------------------------------+----------------------------------------------+------------------------------------------------+
|            sku            | embroidery_imprint_id_setup_sku_price_1 | embroidery_imprint_id_running_sku_price_1 | screen_print_imprint_id_setup_sku_price_1 | screen_print_imprint_id_running_sku_price_1 | laser_engraving_imprint_id_setup_sku_price_1 | laser_engraving_imprint_id_running_sku_price_1 |
+---------------------------+-----------------------------------------+-------------------------------------------+-------------------------------------------+---------------------------------------------+----------------------------------------------+------------------------------------------------+
| IP-205-1067-16            | NULL                                    | NULL                                      |                                        55 | 0.99                                        |                                           45 | 0.59                                           |
| IP-205-1067-16-39423495   | NULL                                    | NULL                                      |                                        55 | 0.99                                        |                                           45 | 0.59                                           |
| IP-205-1067-16-1272347    | NULL                                    | NULL                                      |                                        55 | 0.99                                        |                                           45 | 0.59                                           |
| IP-205-1067-16-56185      | NULL                                    | NULL                                      |                                        55 | 0.99                                        |                                           45 | 0.59                                           |
| IP-205-1067-16-1706399150 | NULL                                    | NULL                                      |                                        55 | 0.99                                        |                                           45 | 0.59                                           |
+---------------------------+-----------------------------------------+-------------------------------------------+-------------------------------------------+---------------------------------------------+----------------------------------------------+------------------------------------------------+

You can see that the product table has screen_print_imprint_id which matches the imprint_method_id in the imprint_locations table.

`WHERE imprint_locations.setup_sku = CONCAT('setup_sku_', product.sku)` — Barmar, Aug 01 '17 at 00:15
You are expected to try to **write the code yourself**. After [doing more research](https://meta.stackoverflow.com/q/261592/1011527) if you have a problem **post what you've tried** with a **clear explanation of what isn't working** and provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Read [How to Ask a good question](http://stackoverflow.com/help/how-to-ask). Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). — Sloan Thrasher, Aug 01 '17 at 00:15
Hey Barmar, ABC and setup sku ABC are fictitious. I have a product.sku called he-123-h100 and a imprint_locations.setup_sku called 924-gee. — Boris, Aug 01 '17 at 00:32
Then your question isn't clear. How are those two SKUs supposed to be relalated to each other? — Barmar, Aug 01 '17 at 01:26
You also haven't said what you're trying to do with these two tables. I assumed you were trying to join them based on the similar SKUs. — Barmar, Aug 01 '17 at 01:27
In the last line I say what I'm looking for. In bold. I need to most likely create a temporary table then use that table against the original table. So connect and create the temp product&imprint_locations table. Then take that and get the product.price_1 of the imprint_locations.setup_sku where imprint_locations.imprint_method_id=product.full_color_imprint_id. I'm not sure. Basically I want to display the product.sku and the product.sku's product.full_color_imprint_id's imprint_location.imprint_id's imprint_locations.setup_sku's product.price_1 — Boris, Aug 01 '17 at 03:30
If you can share the relations as output of the tables containing sample data instead of explaining the relationships in words, things will be more clear. Also please share the expected output with the sample data. — Aniket V, Aug 01 '17 at 06:37
@Barmar - Hopefully the added tables will help. I appreciate your work. — Boris, Aug 01 '17 at 17:37
How do you decide whether to use `Body Right` or `Body Left` to link the rows? In your example they both link to the same rows, but I assume that isn't always the case. — Barmar, Aug 01 '17 at 19:57
@Barmar Body Right and Body Left are actually not useful and should've been left out... They are just locations to put the screen print or laser engraving and they will change. Not sure if I need to explain something more? — Boris, Aug 01 '17 at 19:58
But there are two rows in `imprint_locations` for each `imprint_method_id`. Which one should be used to link to the `product` table to get the price for the `running_sku` and `setup_sku`? — Barmar, Aug 01 '17 at 20:03
I guess it should be grouped by `imprint_method_id` because I only need one of them. Sometimes there are 5 duplicate ids in that column because there are 5 different locations. Does this help? — Boris, Aug 01 '17 at 20:06
But what if they link to different `running_sku` or `setup_sku`, which one should be used? — Barmar, Aug 01 '17 at 20:08

Barmar · Accepted Answer · 2017-08-01T20:18:10.580

You need to join with the product table twice, using the imprint_locations table as an intermediate relation table.

You could use a separate join for each ID field you want to link, or you can use a method from MySQL pivot table to join once and pivot the prices into the appropriate columns.

SELECT DISTINCT p1.sku, 
    MAX(IF(p1.embroidery_imprint_id = l.imprint_method_id AND p2.sku = l.setup_sku, p2.price_1, NULL)) AS embroidery_imprint_id_setup_sku_price_1,
    MAX(IF(p1.embroidery_imprint_id = l.imprint_method_id AND p2.sku = l.running_sku, p2.price_1, NULL)) AS embroidery_imprint_id_running_sku_price_1,
    MAX(IF(p1.screen_print_imprint_id = l.imprint_method_id AND p2.sku = l.setup_sku, p2.price_1, NULL)) AS screen_print_imprint_id_setup_sku_price_1,
    MAX(IF(p1.screen_print_imprint_id = l.imprint_method_id AND p2.sku = l.running_sku, p2.price_1, NULL)) AS screen_print_imprint_id_running_sku_price_1,
    MAX(IF(p1.laser_engraving_imprint_id = l.imprint_method_id AND p2.sku = l.setup_sku, p2.price_1, NULL)) AS laser_engraving_imprint_id_setup_sku_price_1,
    MAX(IF(p1.laser_engraving_imprint_id = l.imprint_method_id AND p2.sku = l.running_sku, p2.price_1, NULL)) AS laser_engraving_imprint_id_running_sku_price_1
FROM product AS p1
JOIN imprint_locations AS l ON l.imprint_method_id IN (p1.embroidery_imprint_id, p1.screen_print_imprint_id, p1.laser_engraving_imprint_id)
JOIN product AS p2 ON p2.sku IN (l.running_sku, l.setup_sku)
GROUP BY p1.sku

DEMO

I see it works in your demo, but it simply keeps timing out when I try and run it. :( Thanks for your efforts. I'm not sure what to do about it. Maybe I should just get one at a time? embroidery_imprint_id_setup_sku_price_1 then export it... then get imprint_method_id_setup_sku_price_1 then export it... — Boris, Aug 01 '17 at 21:36
Make sure you have indexes on the columns being used in the `ON` clauses. — Barmar, Aug 01 '17 at 21:39
I have indexed every single column. It still runs and won't complete. Any other suggestions? — Boris, Aug 02 '17 at 18:51
You might have to split it up into separate `LEFT JOIN`s for each column that you're joining on. It's not complicated, just very verbose for all those combinations. — Barmar, Aug 02 '17 at 18:57
I couldn't use your statement, but it does work so I'm giving you the correct answer. Thanks for your time. — Boris, Aug 02 '17 at 22:43

Join tables when one column has a prefix

1 Answers1