Results 1 to 1 of 1
  1. #1
    Join Date
    Feb 2014
    Posts
    2

    Unanswered: How to use a subquery/join with three tables in mysql?

    testplan

    id
    testplan_id
    tcversion_id
    platform_id

    executions

    1. id
    2. build_id
    3. testplan_id
    4. tcversion_id
    5. platform_id
    6. status
    7. execution_ts
    8. tester_id

    builds

    1. id
    2. testplan_id
    3. name
    4. active

    I have three tables as shown above. testplan table stores all the cases executions tables store execution related data builds table stores builds related data

    I want to retrive data from these table in such a way that it should display all rows from testplan table, for each row if any execution is there from execution's table and only the build names from builds tables.

    I used following query

    SELECT
    tptcv.id,
    tptcv.testplan_id,
    e.tcversion_id,
    tptcv.platform_id,
    tptcv.creation_ts,
    b.name AS Build_name,
    e.status AS status,
    e.execution_ts,
    e.tester_id
    from testplan tptcv
    inner join executions e on tptcv.tcversion_id=e.tcversion_id and tptcv.platform_id=e.platform_id and tptcv.testplan_id=e.testplan_id
    inner join builds b on tptcv.testplan_id=b.testplan_id
    WHERE
    b.active=1


    This query doesn't giving me the expected result as per the row count needed compare to rows in testplan table.

    Result should show all rows from testplan table and matching data from executions and builds table. For non-matching value it should display NULL values

    I will appreciate if some help me in this case.

    Thanks in advance.
    Attached Thumbnails Attached Thumbnails testplan.png   executions.png   builds.png   ExpectedResults.png   QueryResult.png  

    Last edited by kjaware; 02-05-14 at 15:13.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •