MySQL combine 2 rows into 1 with diffrent values

Question

I have 2 tables and I want to combine these 2 tables into 1 (view) with 1 row per order. I want to check if an "id" from "order_address" table is linked with the "billing_address_id" from the "order" table. If thats the case this address is a billing-address and if its not linked is a shipping-address.

I tried a lot of things but I cant combine 2 rows with different values. See below. Is there some posibility to do this?

Order table:

id	order_number	billing_address_id	order_date	order_amount
1	1001	147	2023-04-14	81.88
2	1002	369	2023-04-18	29.99

Order_address table:

id	order_id	first_name	last_name	street	zipcode	city
147	1	John	Doe	Washington Street 2	33101	Miami
369	2	Dave	Dunkin	Power Street 33a	10010	New York
465	2	Marry	Dunkin	Power Street 14	14205	Buffalo

Actual output:

order_number	first_name	last_name	street	zipcode	city
1001	Peter	Paper	Washington Street 2	33101	Miami
1002	John	Dunkin	Power Street 33a	10010	New York
1002	Marry	Dunkin	Other Street 14	14205	Buffalo

Wanted Output:

order_number	first_name	last_name	street	zipcode	city	ship_first_name	ship_last_name	ship_street	ship_zipcode	ship_city
1001	Peter	Paper	Washington Street 2	33101	Miami
1002	John	Dunkin	Power Street 33a	10010	New York	Marry	Dunkin	Other Street 14	14205	Buffalo

How do we distinguish between the first name and the ship first name? — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 20, 2023 at 15:05
If the "ID" from the "order_address" table is linked to the "billing_address_id" from the "order" table, it is a billing address, otherwise it is a shipping address. As mentioned in the start post ;) — Kuba
– Kuba, Commented Apr 20, 2023 at 15:20
order.billing_address_id = order_address.id => billing-address, order.billing_address_id != order_address.id => shipping-address — Kuba
– Kuba, Commented Apr 20, 2023 at 15:27
wouldn't it make more sense to just have 1 column indicating shipping or billing? — DannySlor
– DannySlor, Commented Apr 20, 2023 at 15:30
Ofc it would be better but this is what the software serves you as the Database structure. So I need to deal with it. — Kuba
– Kuba, Commented Apr 20, 2023 at 15:32

Renuka Jatoth · Accepted Answer · 2023-04-20 15:44:05Z

1

Check this view query. This will generate data as you expected.

CREATE VIEW order_with_addresses_vw AS 
SELECT 
    o.order_number, 
    b.first_name AS billing_first_name, 
    b.last_name AS billing_last_name, 
    b.street AS billing_street, 
    b.zipcode AS billing_zipcode, 
    b.city AS billing_city, 
    s.first_name AS shipping_first_name, 
    s.last_name AS shipping_last_name, 
    s.street AS shipping_street, 
    s.zipcode AS shipping_zipcode, 
    s.city AS shipping_city
FROM 
    orders o
LEFT JOIN 
    order_address b ON o.billing_address_id = b.id
LEFT JOIN 
    order_address s ON o.billing_address_id != s.id AND o.id = s.order_id;

Then you can run the select command for viewing the generated table.

SELECT * FROM order_with_addresses_vw ;

This is the output :

order_number	billing_first_name	billing_last_name	billing_street	billing_zipcode	billing_city	shipping_first_name	shipping_last_name	shipping_street	shipping_zipcode	shipping_city
1001	John	Doe	Washington Street 2	33101	Miami	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)
1002	Dave	Dunkin	Power Street 33a	10010	New York	Marry	Dunkin	Power Street 14	14205	Buffalo

edited Apr 20, 2023 at 15:44

answered Apr 20, 2023 at 15:33

Renuka Jatoth

362 bronze badges

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

1 Comment

Kuba Over a year ago

Wow so quick and a clean query. This works perfect for me. The key here is the 2 left joins right? Could you explain a little bit, if not its cool. Thank you sir!

Collectives™ on Stack Overflow

MySQL combine 2 rows into 1 with diffrent values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related