CODEWITHSUNDEEP
mysqlsql
Jack Robson
Jack Robson

Reputation: 2302

Merge Two Datasets By Common Key SQL

I have two datasets:

skus table:

old_product_id  sku
       1        AAA

price_tables table

old_product_id  option_one  option_two
       1          3 Days    One Color
       1          7 Days    One Color

I thought I could merge like this:

SELECT old_product_id, null, option_one, option_two
FROM price_tables
GROUP BY old_product_id
UNION ALL
SELECT old_product_id, sku, null, null
FROM skus
GROUP BY old_product_id

But I get the following result:

old_product_id  NULL option_one  option_two
       1        NULL  3 Days     One Color
       1        AAA    NULL        NULL

Expected output:

old_product_id  Sku option_one  option_two
       1        AAA   3 Days     One Color
       1        AAA   7 Days     One Color

Upvotes: 2

Views: 4088

Answers (3)

Mohd Aman
Mohd Aman

Reputation: 220

Try This Query 

SELECT old_product_id, sku, '' As option_two FROM price_tables GROUP BY old_product_id, sku
UNION ALL
SELECT old_product_id, option_one, option_two FROM skus GROUP BY old_product_id, option_one, option_two

This will give you appropriate result. Hope it will resolve your problem

Upvotes: 0

stepio
stepio

Reputation: 905

What about trying simple join?

SELECT p.old_product_id, s.sku, p.option_one, p.option_two FROM price_tables p INNER JOIN skus s ON s.old_product_id = p.old_product_id

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You don't need a union you need a join to get your expected output

SELECT 
  s.old_product_id,
  s.sku,
  p.option_one,
  p.option_two 
FROM
  skus s
  JOIN price_tables p USING (old_product_id)

Demo

Upvotes: 1

Related Questions