MySQL Select Data from 5 Different Tables

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'




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`

+-------+-------------+------------------+-----------------+--------------+----------+--------------------------+------------+--------------+------------+
|  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 |
+-------+-------------+------------------+-----------------+--------------+----------+--------------------------+------------+--------------+------------+

Code (markup):

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


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

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 |
+-------+--------------+-----------------+--------------+----------+-------+

Code (markup):

`Table Sic_codes` doesn’t have a direct relation with `Table Notices`. But it has with `Table Companies`.

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   |
+-----+-------+--------------------------+------------------------+----------------+

Code (markup):

`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`

Table: Notice_insolvency_practitionar_ID .

Notice_insolvency_practitionar_ID. Notice_ID = Notices. ID
+-----------+-----------------------------+
| Notice_ID | Insolvency_Practiotionar_ID |
+-----------+-----------------------------+
|     96008 |                        1048 |
|     96014 |                         725 |
|     96032 |                         548 |
+-----------+-----------------------------+
[code]

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

[code]
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 | conard@conard.com |
|  725 | Mr. Andrew | Andrew Ltd | New Road   |  03243434 | andrew@andrew.com |
|  548 | Mr. Mark   | Mark Ltd   | Hamilton   | 038743287 | mark@mark.com     |
+------+------------+------------+------------+-----------+-------------------+

Code (markup):

My expected Output

+-------+-------------+------------------+-----------------+--------------+----------+--------------------------+------------+--------------+------------+
|  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 |
+-------+-------------+------------------+-----------------+--------------+----------+--------------------------+------------+--------------+------------+

Code (markup):

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 n.id
, n.gazette_notice_id, n.notice_code, n.company_number, n.publication_date, c.company_name, c.registered_address_town, c.registered_address_postcode, c.sic_1, s.description, s.division, s.section, i.name practionar_name, i.company practitioner_company, i.address prac_address, i.phone
FROM notices n
LEFT
JOIN companies c
ON c.company_number = n.company_number
LEFT
JOIN sic_codes
ON s.code = c.sic_1
LEFT
JOIN notice_insolvency_practitioners ni
ON ni.notice_id = n.id
LEFT
JOIN insolvency_practitioners i
ON i.id = ni.insolvency_practitioner_id
WHERE n.publication_date >'2020-05-01'
AND n.publication_date <'2020-05-31'[/cpde]

Thanks in advance.

Code (markup):

 

Try this query:
Change the part between <and> signs to actual field name, like:
c.<field name> becomes
c.company_name

If you want to select 2 field names, then like this:
c.company_name, c.company_owner, etc
Query:


select
n.id, n.<notices table fields>,
c.<company tables fields>,
s.sic_code,
i.<fields of table Insovency_Practionar>
from
noticesTable as n,
Notice_insolvency_practitionar_ID as np,
companyTable as c,
sicTable as s,
Insovency_Practionar as i,
where
n.publication_date >'2020-03-01' and n.publication_date < '2020-03-31'
and
c.Company_number = n. Company_Number
and
c.Sic1 = s.Code
and
np. Notice_ID = n. ID
and
i.ID = np.Insolvency_Practiotionar_ID
group by n.id
limit <whatever>

 

Made a little edit in the query, "company_number" was written as "company_ number".