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
    e.tcversion_id AS EXECUTION_TCVERSION,
    b.name AS BUILD_NAME,
    e.status AS EXECUTION_STATUS,
    e.execution_ts AS EXECUTION_DATE,
    e.tester_id AS EXECUTED_BY
    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

    enter code here

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

    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  

Posting Permissions

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