1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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 ?
    SEMrush

    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
    SEMrush
  2. JEET

    JEET Notable Member

    Messages:
    3,377
    Likes Received:
    376
    Best Answers:
    16
    Trophy Points:
    235
    #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:
    26,241
    Likes Received:
    3,867
    Best Answers:
    108
    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,377
    Likes Received:
    376
    Best Answers:
    16
    Trophy Points:
    235
    #4
    Made a little edit in the query, "company_number" was written as "company_ number".
     
    JEET, Jun 3, 2020 IP