MySQL Select Data from 5 Different Tables

Discussion in 'Databases' started by Bashabi, Jun 2, 2020.

  1. #1
    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):
     
    Last edited by a moderator: Jun 3, 2020
    Bashabi, Jun 2, 2020 IP
  2. JEET

    JEET Notable Member

    Messages:
    3,832
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #2
    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>
     
    Last edited: Jun 3, 2020
    JEET, Jun 3, 2020 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    28,830
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #3
    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.
     
    sarahk, Jun 3, 2020 IP
    JEET likes this.
  4. JEET

    JEET Notable Member

    Messages:
    3,832
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #4
    Made a little edit in the query, "company_number" was written as "company_ number".
     
    JEET, Jun 3, 2020 IP