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

testplan

  1. id
  2. testplan_id
  3. tcversion_id
  4. 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 

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.

Answers


Remove WHERE b.active = 1 and check whether you get expected answer (because build table do not have a column called active)


Need Your Help

Multiple selection Calendar for jsf

calendar selection

Is there an already implemented Calendar for JSF which allows the user to select multiple dates? I need a calendar in which I can select more than one date.

Definition list with inline pairs

css list line inline definition

I'm trying to create a definition list of term-definition pairs, each pair existing on a single, separate line. I've tried making dts and dds display:inline, but then I lose the line breaks between...