CODEWITHSUNDEEP
sqlleft-join
Abdus Sattar Bhuiyan
Abdus Sattar Bhuiyan

Reputation: 3074

How to join left while there are more than one foreign key of comma seperated

I have the following two tables(orders and psettings): enter image description here enter image description here

I want to join Orders table left with psettings table. It works when product_id is only one in Order table. My query is:

SELECT `Order`.*, `products`.*, `psettings`.*, `City`.`name`, `Location`.`name` FROM `amrajegeachi`.`orders` AS `Order` LEFT JOIN `amrajegeachi`.`cities` AS `City` ON (`Order`.`city_id` = `City`.`id`) LEFT JOIN `amrajegeachi`.`locations` AS `Location` ON (`Order`.`location_id` = `Location`.`id`) LEFT JOIN `products` ON `Order`.`product_id` = `products`.`id` LEFT JOIN `psettings` ON `Order`.`product_id` = `psettings`.`product_id` WHERE `status` = 'No contact'

How can do the same task for multiple comma separated product_id in orders table?

N.B: There are two more left join relation tables: 'cities' and 'locations'.

Upvotes: 1

Views: 41

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726799

You cannot do it, because your database design is flawed. One should avoid storing multiple things in a single field: it seems like a smart decision initially, yet it inevitably comes back to make your life difficult.

A proper way to design this data model is to make a separate table for product_ids, like this:

create table order_product (
    order_id int not null
,   product_id int not null
)

This table would look like this in your scenario:

order_id product_id
-------- ----------
       1          1
       2          1
       3          1
       4          1
       4          2
       4          3

Now you could formulate your join with order_product, making sure that the join works for multi-product orders as well:

SELECT `Order`.*, `products`.*, `psettings`.*, `City`.`name`, `Location`.`name`
FROM `amrajegeachi`.`orders` AS `Order`
LEFT JOIN `amrajegeachi`.`cities` AS `City` ON (`Order`.`city_id` = `City`.`id`)
LEFT JOIN `amrajegeachi`.`locations` AS `Location` ON (`Order`.`location_id` = `Location`.`id`)
-- This line takes care of dealing with multiple orders:
LEFT JOIN order_product ON `Order`.id=order_product.order_id
LEFT JOIN `products` ON order_product.product_id = `products`.`id`
LEFT JOIN `psettings` ON `Order`.`product_id` = `psettings`.`product_id` 
WHERE `status` = 'No contact'

Upvotes: 1

Related Questions