CODEWITHSUNDEEP
mysql
Jean Nemarre
Jean Nemarre

Reputation: 19

MySQL - How to make one single SQL request for this case?

I have been trying to find a solution for the following SQL request for days, without success.

My situation:

I have 3 tables (link: https://i.sstatic.net/BswG0.png)

The table Offers contains 2 foreign keys:

In case the offer is linked to a request sent by a company, the field offers_fk_requests contains a value and the field offers_fk_companies is null.

In case the offer is submitted directly to a company without a request, the field offers_fk_requests is null and the field offers_fk_companies contains a value.

I would like to make a SELECT on the table Offers, to get all entries for offers submitted directly to companies (without any requests) but also for offers submitted following requests from companies.

I came with this request:

SELECT *
FROM `offers`

LEFT JOIN `requests` ON `offers_fk_requests` = requests.`id`
JOIN `companies` AS companies1 ON requests.`fk_companies` = companies1.`companies_id`

LEFT JOIN `companies` AS companies2 ON offers.`fk_companies` = companies2.`companies_id`

WHERE ...

I get different results when I do the request above than when I separate it into 2 differents requests:

SELECT *
FROM `offers`

LEFT JOIN `requests` ON `offers_fk_requests` = requests.`id`
JOIN `companies` AS companies1 ON requests.`fk_companies` = companies1.`companies_id`

WHERE ...

SELECT *
FROM `offers`

LEFT JOIN `companies` AS companies2 ON offers.`fk_companies` = companies2.`companies_id`

WHERE ...

Basically, there are 2 different paths: one going from table offers to table companies, and another one going from table offers to table companies but by passing through the intermediate table requests.

How can I achieve the same result with my single request than with those separate ones combined?

I can't use an UNION as the number of fields is different. I don't know where else to find a solution...

Thanks in advance.

EDIT

It seems that, if I take my original request, and I use LEFT OUTER JOIN for all the joins, it works.

SELECT * FROM offers

LEFT OUTER JOIN requests ON offers_fk_requests = requests.id LEFT OUTER JOIN companies AS companies1 ON requests.fk_companies = companies1.companies_id

LEFT OUTER JOIN companies AS companies2 ON offers.fk_companies = companies2.companies_id

WHERE ...

@Marcel: Thank you for sending me in the right direction!

Upvotes: 1

Views: 62

Answers (2)

etsuhisa
etsuhisa

Reputation: 1758

If you use an UNION, missing fields can be completed with NULL.

SELECT *
FROM `offers`

LEFT JOIN `requests` ON `offers_fk_requests` = requests.`id`
JOIN `companies` AS companies1 ON requests.`fk_companies` = companies1.`companies_id`
UNION ALL SELECT null, null, null, `fk_companies`, `companies_id`
FROM `offers`

LEFT JOIN `companies` AS companies2 ON offers.`fk_companies` = companies2.`companies_id`
WHERE `companies_id` IS NOT NULL;

To get all companies_id in the same column, you can use the following query.

SELECT `requests`.`id` AS `requests_id`, coalesce(`requests`.`fk_companies`, `companies`.`companies_id`) AS `companies_id`
FROM `offers`

LEFT JOIN `companies` ON `offers`.`fk_companies` = `companies`.`companies_id`
LEFT JOIN `requests` ON `offers`.`offers_fk_requests` = `requests`.`id`;

See db fiddle.

Upvotes: 0

Marcel
Marcel

Reputation: 15742

Based on the OP's comment that all should be in one go, I suggest to simply do

SELECT * 
FROM offers
LEFT OUTER JOIN companies ON companyId /*simplified*/
LEFT OUTER JOIN requests ON requestId /*simplified*/

Left outer joins only join data when there is a match, otherwise leaving the fields empty. So you end up with all offers, having the available fields filled.

However, with the current design, for those that have a request, the company data is empty. I would suggest, to set the company foreign key also on those that are based on a request, causing this key to be mandatory. Because of the request, they are tied to a company anyway.

Upvotes: 0

Related Questions