CODEWITHSUNDEEP
mysql
Nikhil Gupta
Nikhil Gupta

Reputation: 192

Retrieving data from two tables in one row with MySQL

I'm trying to get date from two tables, but problem is that they are returning multiple rows for individual id. I have tables like these:-

Transaction
-----------
tid|startdate|enddate|status
100|2-9-2015|2-9-2015|success
200|1-9-2015|1-9-2015|failed
300|31-8-2015|31-8-2015|success

Test_Details
------------
id|tid|test_name|test_status
1|100|Test1|yes
2|100|Tes2|no
3|100|Test3|yes
4|200|Test1|no
5|200|Tes2|no
6|200|Test3|yes
7|300|Test1|yes
8|300|Tes2|no
9|300|Test3|no

Desired result :-

tid|startdate|enddate|status|Test1|Test2|Test3
100|2-9-2015|2-9-2015|success|yes|no|yes
200|1-9-2015|1-9-2015|failed|no|no|yes
300|31-8-2015|31-8-2015|success|yes|no|no

I am using this query:-

select distinct ts.tid as tid, ts.startdate as startdate,
ts.enddate as enddate, ts.status as status, 
CASE WHEN (td.test_name='Test1') THEN test_status END as Test1,
CASE WHEN (td.test_name='Test2') THEN test_status END as Test2,
CASE WHEN (td.test_name='Test3') THEN test_status END as Test3
from transation ts, test_details td where ts.tid =td.id

But I'm getting multiple rows. Here is SQLFiddle demo

Upvotes: 1

Views: 62

Answers (4)

vhu
vhu

Reputation: 12798

As long as test_details isn't a temporary table you can do the following:

SELECT ts.tid, ts.startdate, ts.enddate, ts.status,
  t1.test_status Test1,
  t2.test_status Test2,
  t3.test_status Test3
 FROM transaction ts
  LEFT JOIN test_details t1 ON (t1.tid=ts.tid AND t1.test_name='Test1')
  LEFT JOIN test_details t2 ON (t2.tid=ts.tid AND t2.test_name='Test2')
  LEFT JOIN test_details t3 ON (t3.tid=ts.tid AND t3.test_name='Test3');

This is under the assumption that transaction.tid is unique and (tid, test_name) pair in test_details is unique.

EDIT: In reply to OPs comment "It's working but it is slow on large db" I'd suggest to add indexes to test_details.tid and test_details.test_name:

ALTER TABLE test_details ADD INDEX (tid);
ALTER TABLE test_details ADD INDEX (test_name);

Also, if all three tests are known to exist for all transactions you may want to use JOIN instead of LEFT JOIN

SELECT ts.tid, ts.startdate, ts.enddate, ts.status,
  t1.test_status Test1,
  t2.test_status Test2,
  t3.test_status Test3
 FROM transaction ts
  JOIN test_details t1 ON (t1.tid=ts.tid AND t1.test_name='Test1')
  JOIN test_details t2 ON (t2.tid=ts.tid AND t2.test_name='Test2')
  JOIN test_details t3 ON (t3.tid=ts.tid AND t3.test_name='Test3');

Upvotes: 1

Zafar Malik
Zafar Malik

Reputation: 6844

You can use your own query just after adding group by clause-

select distinct ts.tid as tid, ts.startdate as startdate,
ts.enddate as enddate, ts.status as status, 
CASE WHEN (td.test_name='Test1') THEN test_status END as Test1,
CASE WHEN (td.test_name='Test2') THEN test_status END as Test2,
CASE WHEN (td.test_name='Test3') THEN test_status END as Test3
from transation ts, test_details td 
where ts.tid =td.id 
group by ts.tid;

Upvotes: 0

splash58
splash58

Reputation: 26153

select 
    ts.tid as tid, 
    ts.startdate as startdate,
    ts.enddate as enddate, 
    ts.status as status, 
    group_concat(test1) test1, 
    group_concat(test2) test2, 
    group_concat(test3) test3 
  from 
    (select 
        ts.tid as tid, 
        ts.status as status, 
        CASE WHEN (td.test_name='Test1') THEN test_status END as Test1,
        CASE WHEN (td.test_name='Tes2') THEN test_status END as Test2,
        CASE WHEN (td.test_name='Test3') THEN test_status END as Test3
      from 
         transation ts 
       join 
         test_details td 
       on ts.tid=td.tid
      ) t 
 group by tid

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You can use a GROUP BY query, for example with GROUP_CONCAT aggregate function:

SELECT
  ts.tid as tid,
  ts.startdate as startdate,
  ts.enddate as enddate,
  ts.status as status,
  GROUP_CONCAT(test_status ORDER BY td.test)
FROM
  transation ts LEFT JOIN test_details td ON ts.tid =td.id
GROUP BY
  ts.tid,
  ts.startdate,
  ts.enddate,
  ts.status

or this query if you want to have status in different columns:

SELECT
  ts.tid as tid,
  ts.startdate as startdate,
  ts.enddate as enddate,
  ts.status as status, 
  MAX(CASE WHEN (td.test_name='Test1') THEN test_status END) as Test1,
  MAX(CASE WHEN (td.test_name='Test2') THEN test_status END) as Test2,
  MAX(CASE WHEN (td.test_name='Test3') THEN test_status END) as Test3
FROM
  transation ts LEFT JOIN test_details td ON ts.tid =td.id
GROUP BY
  ts.tid,
  ts.startdate,
  ts.enddate,
  ts.status

Upvotes: 0

Related Questions