MYSQL Select - Select Data from 5 different tables

[Bashabi Sarker]

I have to select data from 5 different tables. The main table is `Notices`. Following are the rows from the `Table Notices` which I want to select

SELECT * FROM `notices` WHERE notices.publication_date >'2020-03-01' and notices.publication_date < '2020-03-31'


<pre>Table: Notices
+-------+------------------+-------------+-----------------+------------------+
| ID | Gazzet_notice_id | Notice_Code | Company_Number | Publication_Date |
+-------+------------------+-------------+-----------------+------------------+
| 96008 | 3508363 | 2410 | 09844265 | 2020-03-02 |
| 96014 | 3508298 | 2410 | 02640968 | 2020-03-02 |
| 96032 | 3508227 | 2410 | 03666759 | 2020-03-02 |
+-------+------------------+-------------+-----------------+------------------+
</pre>


Now I also have to select related information for these rows from different tables. Some tables have a direct relation with Table Notice and some tables are related via other tables.

My expected output is the following. Where I will have to take `Company Name`, `SIC Code` from `Table Companies` ; `SIC Code Description` from `Table Sic_Codes`; `Prac.Name`, `Prac.Company`, `Prac.Phone` from `Table Insovency_Practionar`

<pre>
+-------+-------------+------------------+-----------------+--------------+----------+--------------------------+------------+--------------+------------+
| ID | Notice Code | Publication_Date | Company_Number | Company Name | SIC Code | Sic Code Description | Prac. Name | Prac.company | Prac_Phone |
+-------+-------------+------------------+-----------------+--------------+----------+--------------------------+------------+--------------+------------+
| 96008 | 2410 | 2020-03-02 | 09844265 | ATL Logistic | 49410 | Freight Transport | Mr. Conard | Conard Ltd | 01234567 |
| 96014 | 2410 | 2020-03-02 | 02640968 | New-Tonne | 28220 | Manufacturer Lifting… | Mr. Andrew | Andrew Ltd | 03243434 |
| 96032 | 2410 | 2020-03-02 | 03666759 | Sonataine | 41100 | Development and Building | Mr. Mark | Mark Ltd | 038743287 |
+-------+-------------+------------------+-----------------+--------------+----------+--------------------------+------------+--------------+------------+

</pre>

Below I have described the relation between each Table with `Table Notices`


`Table Companies` has a direct relation with `Table Notices`


<pre>
Table: Companies

Companies.Company_number = Notices. Company_ Number
+-------+--------------+-----------------+--------------+----------+-------+
| ID | Company_Name | Company_Number | Address | postcode | Sic1 |
+-------+--------------+-----------------+--------------+----------+-------+
| 81983 | ATL Logistic | 09844265 | Scot Street | DY4 7AG | 49410 |
| 81989 | New-Tonne | 02640968 | Withins Road | WA11 9UD | 28220 |
| 82004 | Sonataine | 03666759 | Vastry Road | TN14 5EL | 41100 |
+-------+--------------+-----------------+--------------+----------+-------+
</pre>

`Table Sic_codes` doesn’t have a direct relation with `Table Notices`. But it has with `Table Companies`.
<pre>
Table: Sic_Codes.

Companies.Sic1 = Sic_code.Code

+-----+-------+--------------------------+------------------------+----------------+
| ID | Code | Description | Division | Section |
+-----+-------+--------------------------+------------------------+----------------+
| 468 | 49410 | Freight Transport | Land Transport | Transportation |
| 262 | 28220 | Manufacture of Lifting… | Machinery and Eqipment | Manufacture |
| 334 | 41100 | Development and Building | Construction Building | Construction |
+-----+-------+--------------------------+------------------------+----------------+
</pre>

`Table Insovency_Practionar` does not have a direct relation with `Table Notices`. There is another `Table Notice_insolvency_practitionar_ID` to create relation between these two tables `Table Insovency_Practionar and Table Notices`

<pre>
Table: Notice_insolvency_practitionar_ID .

Notice_insolvency_practitionar_ID. Notice_ID = Notices. ID
+-----------+-----------------------------+
| Notice_ID | Insolvency_Practiotionar_ID |
+-----------+-----------------------------+
| 96008 | 1048 |
| 96014 | 725 |
| 96032 | 548 |
+-----------+-----------------------------+
</pre>

I have to use the above table to fetch relevant rows from the below table.

<pre>
Table: Insovency_Practionar .

Insovency_Practionar.ID = Notice_insolvency_practitionar_ID. Insolvency_Practiotionar_ID
+------+------------+------------+------------+-----------+-------------------+
| ID | Name | Company | Address | Phone | Email |
+------+------------+------------+------------+-----------+-------------------+
| 1048 | Mr. Conard | Conard Ltd | Birmingham | 01234567 | [e-mail] |
| 725 | Mr. Andrew | Andrew Ltd | New Road | 03243434 | [e-mail] |
| 548 | Mr. Mark | Mark Ltd | Hamilton | 038743287 | [e-mail] |
+------+------------+------------+------------+-----------+-------------------+

</pre>


My expected Output

<pre>
+-------+-------------+------------------+-----------------+--------------+----------+--------------------------+------------+--------------+------------+
| ID | Notice Code | Publication_Date | Company_Number | Company Name | SIC Code | Sic Code Description | Prac. Name | Prac.company | Prac_Phone |
+-------+-------------+------------------+-----------------+--------------+----------+--------------------------+------------+--------------+------------+
| 96008 | 2410 | 2020-03-02 | 09844265 | ATL Logistic | 49410 | Freight Transport | Mr. Conard | Conard Ltd | 01234567 |
| 96014 | 2410 | 2020-03-02 | 02640968 | New-Tonne | 28220 | Manufacturer Lifting… | Mr. Andrew | Andrew Ltd | 03243434 |
| 96032 | 2410 | 2020-03-02 | 03666759 | Sonataine | 41100 | Development and Building | Mr. Mark | Mark Ltd | 038743287 |
+-------+-------------+------------------+-----------------+--------------+----------+--------------------------+------------+--------------+------------+

</pre>

How to join these tables to select the data with their relevant information.

I have used the following query. But I am not entirely sure. Can you please help ?

SELECT notices.id, notices.gazette_notice_id, notices.notice_code, notices.company_number, notices.publication_date, companies.company_name, companies.registered_address_town, companies.registered_address_postcode, companies.sic_1, sic_codes.description, sic_codes.division, sic_codes.section, insolvency_practitioners.name as practionar_name, insolvency_practitioners.company as practitioner_company, insolvency_practitioners.address as prac_address, insolvency_practitioners.phone FROM notices
LEFT JOIN companies ON notices.company_number = companies.company_number
LEFT JOIN sic_codes ON companies.sic_1 = sic_codes.code
LEFT JOIN notice_insolvency_practitioners ON notices.id = notice_insolvency_practitioners.notice_id
LEFT JOIN insolvency_practitioners ON notice_insolvency_practitioners.insolvency_practitioner_id = insolvency_practitioners.id
Where notices.publication_date >'2020-05-01' and notices.publication_date < '2020-05-31'


Thanks in advance.