SELECT with foreign key with JOIN mysql

Question

I have this table of contracts where I have parent contracts and sub contracts. I want to select all the parent contracts with the number of their subordinate contracts.

SELECT  
sup.id_contract, sup.parent_id, sup.contractor_name
IFNULL(count(sub.id_contract),0) AS orders
FROM Contracts sub 
LEFT JOIN Contracts sup ON sub.parent_id=sup.id_contract 
where sup.deleted !=1 GROUP BY sup.id_contract;

but for me, parent contract it also a contract with parent_id=NULL and its "orders" can be '0'

How can i do that to select also all Contracts with parent_id NULL where amount of orders will be 0?

`count(sub.id_contract)` -> NULL values are not counted. Also, you should use `left join`.. — Georgy, Sep 06 '21 at 08:26
Your GROUP BY is invalid and will raise an error with newer MySQL versions (unless in compatibility mode.) — jarlh, Sep 06 '21 at 09:07
To get true LEFT JOIN result, move the sup.deleted condition from WHERE to ON. — jarlh, Sep 06 '21 at 09:07
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Sep 06 '21 at 10:25

Adrian Kokot · Accepted Answer · 2021-09-06T08:57:21.953

The thing that you are searching for is called left join

SELECT 
sup.id_contract, sup.parent_id, sup.contractor_name, sup.subject_of_contract, sup.type_of_contract, sup.notice_period, sup.delete_parent_contract, 
d.date,
count(sub.id_contract) AS orders
FROM Contracts sup
LEFT JOIN Contracts sub ON sub.parent_id = sup.id_contract
LEFT JOIN (
    SELECT MAX(DATE_FORMAT(d.date, "%Y-%m-%d")) date, d.id_contract FROM 
    Dates d GROUP BY d.id_contract
) d ON sup.id_contract=d.id_contract 
WHERE sup.deleted != 1
GROUP BY sup.id_contract;

score 0 · Answer 2 · answered Sep 06 '21 at 12:04

I want to select all the parent contracts with the number of their subordinate contracts.

You should be able to do this using window functions. The basic idea is to COALESCE() the parent id with the contract id and do a count. This is one more than the number of subcontract, because it includes the parent, so subtract 1:

SELECT c.*
FROM (SELECT c.*,
             COUNT(*) OVER (PARTITION BY COALESCE(c.parent_id, c.id_contract)) - 1 as num_subcontracts
      FROM Contracts c
      WHERE c.deleted <> 1
     ) c
WHERE c.parent_id IS NULL;

Actually, you can do this without the - 1 as well:

SELECT c.*
FROM (SELECT c.*,
             COUNT(c.parent_id) OVER (PARTITION BY COALESCE(c.parent_id, c.id_contract)) as num_subcontracts
      FROM Contracts c
      WHERE c.deleted <> 1
     ) c
WHERE c.parent_id IS NULL;

SELECT with foreign key with JOIN mysql

2 Answers2