CODEWITHSUNDEEP
phpmysqlsql
sashh
sashh

Reputation: 23

how to merge data from 2 rows of different tables based on a common field

I have 2 tables order_management and order_management2 which are almost same... order_id in order_management is auto-incremented where as order_id in order_management2 not. I want to merge data of each field from both tables based on the order_id.

suppose,

order_management table

order_id      customer_name     fone no.      
--------      -------------    ---------
1              A                89000
2              B                78989
3              C                56464

order_management2 table

  order_id        customer_name      fone no.
 ----------      ---------------    ----------
   2                D                 89076
   3                E                 67598
   4                F                 67545

if order_id of both tables is 2 then, it should be displayed like:

 order_id        customer_name      fone no.
 ----------      ---------------    ----------
   2                B,D               78989,89076
   3                C,E               56464,67598

how to do this?

Upvotes: 0

Views: 62

Answers (3)

Mihai
Mihai

Reputation: 26784

SELECT order_id,GROUP_CONCAT(customer_name SEPARATOR ',')as customer_name,
GROUP_CONCAT(`fone no.` SEPARATOR ',')as `fone no.` 
FROM order_management o JOIN order_management2 o2 
ON o.order_id=o2.order_id GROUP BY o.order_id

Upvotes: 1

Parziphal
Parziphal

Reputation: 6902

This one worked for me:

SELECT
  t1.order_id,
  CONCAT(t1.customer_name, ',', t2.customer_name) AS customer_name,
  CONCAT(t1.phone, ',', t2.phone) AS phone_no
FROM order_management t1
JOIN order_management2 t2 ON t1.order_id = t2.order_id
GROUP BY t1.order_id

Upvotes: 1

shadyyx
shadyyx

Reputation: 16055

You can try this query:

SELECT order_id, GROUP_CONCAT(customer_name SEPARATOR ', ') cname, GROUP_CONCAT(fone_no SEPARATOR ', ') fno
FROM order_management o1
    LEFT JOIN order_management2 o2 ON o1.order_id = o2.order_id
GROUP BY o1.order_id

By this You should receive the requested results.

Upvotes: 0

Related Questions