CODEWITHSUNDEEP
mysqlsql
Raja Dhasan
Raja Dhasan

Reputation: 593

Compare output of select statement

My Table1 

    |  Participantsid   |   Validfrom         |    Validto          | 
    | 2,4,1             | 2016-02-21 10:22:00 | 2016-02-21 12:22:00 |
    | 2,4,1             | 2016-03-04 10:00:00 | 2016-03-04 10:25:00 |
    | 2,3,4,1           | 2016-02-19 10:43:00 | 2016-02-19 11:08:00 |
    | 2,3,4,1           | 2016-02-22 11:32:00 | 2016-02-22 11:57:00 |
    | 4,6,5             | 2016-02-20 17:00:00 | 2016-02-20 18:00:00 |
    | 1,2,5             | 2016-02-22 18:00:00 | 2016-02-22 19:00:00 |
    | 2,3,6,1           | 2016-03-23 10:00:00 | 2016-03-23 11:00:00 |
    | 1,2,3,5           | 2016-02-20 12:00:00 | 2016-02-20 14:00:00 |
    | 2,6               | 2016-02-20 12:00:00 | 2016-02-20 13:00:00 |

Another Table2

+--------------+------------+
| EmployeeName | EmployeeID |
+--------------+------------+
| Mathews      |          1 |
| Gerald       |          2 |
| Bravo        |          3 |
| Smith        |          4 |
| George       |          5 |
| Bailey       |          6 |
| Stephen      |          9 |
| Balu c       |         10 |

I have some inputid's=1,5,6. Now I have to compare this input id's with Participantsid from table1 for a given validfrom and validto date and if there is a match I need to get the name corresponding to that id.

Emaple:- inputid's=1,2,3 for from- 2016-02-22 18:00:00, to-2016-02-22 19:00:00 it should give me o/p =mathews,gerald

I am currently writing two select statements and using one for loop which gives me the result but its wrong

I am trying to do it in a single query but no way near to my required output.please help

Upvotes: 2

Views: 76

Answers (1)

Dylan Su
Dylan Su

Reputation: 6065

Desired result can be achieved by using group_concat and find_in_set with sub-query.

SQLFiddle: http://sqlfiddle.com/#!9/ce893/1

select group_concat(EmployeeName) as `o/p`
from Table2 
where FIND_IN_SET(EmployeeID, 
    (select Participantsid from Table1 
    where Validfrom = '2016-02-22 18:00:00' and Validto = '2016-02-22 19:00:00'));

Upvotes: 2

Related Questions