Mysql – Join thesql tables to find siblings and get their notifications

hierarchyMySQL

i have two mysql tabes one for company and one for notifications.
Company

+-----------------------+
| ID |parentID|vaue     |
|1   |0       |22020    |
|2   |1       |23423    |
|3   |2       |22222    |
|4   |2       |23423    |
+-----------------------+

company_notes

+---------------------------------+
|ID   |FromID |  ToID   | NotesID |
|1    |1      |  2      | 33
+---------------------------------+

so far i am able to join the tables to get NotesID of the company who has posted the note. How do i get NoteID of all sibings using company_notes.FromID.

I tried:

Select c1.ID as id,
       ci.Parent as Parent,
       n1.NotesID as noteid
    FROM `company` as c1
    LEFT JOIN comapany as c        on c2.parent=c1.ID
    LEFT JOIN company_notes as n1  on n1.FromID=c1.ID
    WHERE c1.ID=2

but the above code gives me only child notifications and alot of dupilcate values.

how do i find out notifications of all siblings, everyone related to id 2

Sorry if my question was not clear, i have now removed unrequired fields to avoid confusion.

Below is the result that i am expecting: 

+-----------------+
|  ID   | NotesID |
|  3    | 33      |
|  4    | 33      |
+-----------------+

Cheers! and Happy New Year's Eve!

Best Answer

I am not sure I really get your purpose, but this is what would gives the answer you're looking for:

SELECT
    company.ID, company_notes.NotesID
FROM
    company 
    JOIN company_notes ON company_notes.ToId = company.parentID
WHERE
    company.parentID = 2 ;

If you use a DB that implements WITH, this would show it:

WITH company (ID, parentID, value) AS
(
VALUES 
    (1, 0, 22020),
    (2, 1, 23423),
    (3, 2, 22222),
    (4, 2, 23423)
),
company_notes(ID, FromID, ToID, NotesID) AS
(
VALUES
    (1, 1, 2, 33)
)

SELECT
    company.ID, company_notes.NotesID
FROM
    company 
    JOIN company_notes ON company_notes.ToId = company.parentID
WHERE
    company.parentID = 2 ;
Related Question