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>
If @JEET's suggestion doesn't work, hop on over to mysqlfiddle, set up your tables and your sample data and we can have a play.