0

I currently have around 1,000 products in a MySQL table, each of which appears to have been duplicated (1 with a product SKU and one with a product MPN, or Manufacturer Product Number), as shown below:

+--------------+------------+----------------+-----------------+------------------------+
| V_Article_ID | Article_ID | V_Article_Code | Brand_Domain_ID | Prefered_V_Article_ID |
+--------------+------------+----------------+-----------------+------------------------+
|        11003 |       7043 | AA4011         | null            | 11002                  |
|        11002 |       7043 | U-30G-BK       | 101036          | null                   |
+--------------+------------+----------------+-----------------+------------------------+

Ideally what i would like is to combine the two rows into one row for EACH product (as you can see the article_id is the same for both rows), keep the Brand_Domain_ID that is NOT NULL and split the two separate Vendor_Article_Code's into two separate columns; SKU (AA4011) and MPN (U-30G-BK), so the output would looks something like this:

+------------+--------+----------+-----------------+
| Article_ID |  SKU   |   MPN    | Brand_Domain_ID |
+------------+--------+----------+-----------------+
|       7043 | AA4011 | U-30G-BK |          101036 |
+------------+--------+----------+-----------------+

I am currently using the following statement to achieve what i want (if only for a single product based on Article_ID):

SELECT article_id, 
   case WHEN v_article_code REGEXP '^AA' THEN v_article_code END as SKU, 
   case WHEN v_article_code NOT REGEXP '^AA' THEN v_article_code END as MPN,
   Brand_Domain_ID 
FROM vendor_article 
WHERE article_id = 7043

+------------+--------+----------+-----------------+
| Article_ID |  SKU   |   MPN    | Brand_Domain_ID |
+------------+--------+----------+-----------------+
|       7043 |  null  | U-30G-BK |          null   |
+------------+--------+----------+-----------------+
|       7043 | AA4011 |   null   |          101036 |
+------------+--------+----------+-----------------+

My question is this: Is there a way to cut this down so that both rows become a single row, with no null elements and the same Article_ID? Also, is there a quick way in which to iterate through each of the Article_IDs, such as a for loop in PHP? I know how to insert the output into a new table, i'm just not sure the best way to approach the solution.

Any help would be most appreciated.

Improve this question

2 Answers 2

Reset to default
1

Yet another implementation:

select a.article_id, a.v_article_code MKU, b.v_article_code MPN, 
  ifnull(a.brand_domain_id, b.brand_domain_id) brand_domain_id,
  ifnull(c.channel_sku, d.channel_sku) channel_sku
from vendor_article a
join vendor_article b on a.article_id = b.article_id 
  and a.brand_domain_id is not null and b.prefered_v_article_id is not null
left join vendor_article_channel c on a.v_article_id = c.vendor_article_id
left join vendor_article_channel d on b.v_article_id = d.vendor_article_id;

fiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Hi, thank you for your response, i went with this solution in the end. I was wondering how to select a column from another table (table name 'vendor_article_channel.CHANNEL SKU') where the vendor_article_channel.VENDOR_ARTICLE_ID is equal to vendor_article.VENDOR_ARTICLE_ID. I've tried adding this to the select / from statements but it keeps saying that a.article_id no longer exists? I've read in to it all today and i just cant get my head around it? Once again, thank you for your reply!
@SteveVincent, maybe you want a.v_article_id = c.vendor_article_id since there's no vendor_article_id in the vendor_article table in your post?
sorry i shortened vendor_article_id to v_articled_id to make it fit in the table, in the DB its vendor_article_id
@SteveVincent, if you are still having that problem, can you post the other table schema?
certainly, and thanks for your patience, is called 'vendor_article_channel' and looks like this vendor_article_channel_id | vendor_article_id | channel_sku
|
1

You need to JOIN the table to itself, like so:

select sku.article_id as Article_Id,
    sku.v_article_code as SKU,
    mpn.v_article_code as MPN,
    coalesce(sku.brand_domain_id,mpn.brand_domain_id) as Brand_Domain_Id
from vender_article sku join vender_article mpn
    on sku.article_id = mpn.article_id
where sku.v_article_code like 'AA%'
    and mpn.v_article_code not like 'AA%';

edit: Had v_article_id where I should have had v_article_code.

Improve this answer

4 Comments

Thanks for your response, I should have made this clearer but there is only one table (vendor_article) - Does prefixing sku. / mpn. to the column name effect how MySQL treats that table? Also, i need the WHERE clause to find the article_id, can i just append this on the end?
You should give it a run, you can check my syntax (I admit I use Oracle more than MySQL) and I think the results will help you understand it. But in short--this is a "self join": vender_article sku join vender_article mpn. It makes two "copies" of the table (named sku and mpn, so that you can differentiate between them). Here we pick certain types of rows from one copy, and other types of rows from the other copy, and join them together. My example also solves your "ideal" solution, joining together all of your dupe rows, but for a specific ID, yes you can just add that on to the end.
I tried it but it seemed to throw out a lot of random numbers here and there, which is very strange behaviours. However after reading your reply and looking in to it i have learnt alot more, so thank you!
Sorry, I had v_article_id where I should have had v_article_code. I updated it, should work now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.