CODEWITHSUNDEEP
mysqljoin
Valerius Romulus
Valerius Romulus

Reputation: 55

Two querys in one [MYSQL] ONE RESULTS

can you help me guys to join two queries in one? This is my first query:

SELECT estimated_sum_all, story_point_sum_all, time_spent,  reg_date
FROM burndown_snap_all  WHERE project_id='72'

Results:

 | estimated_sum_all | story_point_sum_all | time_spent | reg_date |
 | 300               | 20                  | 20.30      | 2017-09  |
 | 300               | 20                  | 19.30      | 2017-09  |
 | 300               | 20                  | 18.30      | 2017-09  |
 | 300               | 20                  | 15.32      | 2017-09  |

This is my second query:

SELECT time_spent FROM status_history_hours where
project_id = '72'

Results:

| time_spent | 
| 20.30      |
| 20.30      |
| 20.30      |
| 20.30      |

What I wanna to do is to build one mysql query that have to contain select/join to time_spent from the second query. Final table should looks like this:

 | estimated_sum_all | story_point_sum_all | time_spent | reg_date |
 | 300               | 20                  | 20.30      | 2017-09  |
 | 300               | 20                  | 20.30      | 2017-09  |
 | 300               | 20                  | 20.30      | 2017-09  |
 | 300               | 20                  | 20.30      | 2017-09  |

Regards,

Solution for this would be like:

SELECT t1.id, t1.estimated_sum_all, 
t1.story_point_sum_all, t1.time_spent, 
t2.id, t2.time_spent FROM 
burndown_snap_all t1, status_history_hours 
t2 WHERE t1.project_id = 72 AND 
t2.project_id = 72 group by t1.id

But how to group by t2.id in the same time???

Upvotes: 0

Views: 65

Answers (2)

Osama Bari
Osama Bari

Reputation: 598

SELECT estimated_sum_all, story_point_sum_all, time_spent,  reg_date
FROM burndown_snap_all  WHERE project_id='72'
UNION ALL
SELECT time_spent FROM status_history_hours where
project_id = '72'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269483

From the data that you have provided, you can take any matching value from the second table. So, you could do:

SELECT bsa.estimated_sum_all, bsa.story_point_sum_all, bsa.time_spent,
       bsa.reg_date,
       (SELECT MAX(shh.time_spent)
        FROM status_history_hours shh
        WHERE shh.project_id = bsa.project_id
       ) as time_spent
FROM burndown_snap_all bsa
WHERE project_id = 72;

Upvotes: 1

Related Questions