0

I need a help. I have qurum table

qurum table             data table
qurum_id|qurum            id|qurum1
       1|one               1|3
       2|two               2|2
       3|three             3|1
       4|four              4|7
       5|five              5|5,6
       6|six               6|4
       7|seven             7|5
where qurum_id = data.qurum1

The result must be

qurum_id|qurum|qurum1
       1|one  |3
       2|two  |2
       3|three|1
       4|four |7
       5|five |5
     **6|six  |6
       6|six  |4**
       7|seven|5

This is query 

`SELECT qurum_id, qurum,qurum1,
        SUM(CASE WHEN DATE_FORMAT(aa.input_date, '%m') BETWEEN 01 AND 06
           THEN 1 END) AS I,
        SUM(CASE WHEN DATE_FORMAT(aa.input_date, '%m') BETWEEN 07 AND 12
           THEN 1 END) AS II
FROM qurum AS qur
INNER JOIN (SELECT id, qurum1,input_date FROM DATA ) AS aa
            ON qur.qurum_id = aa.qurum1
WHERE DATE_FORMAT(aa.input_date, '%Y') = $year
GROUP BY qurum_id
ORDER BY qurum_id`

The result is here

There is sixth row with qurum1 = 6|7 in the table, but I need to explode 6|7 because qurum_id 6 and qurum_id 7 has different qurum. Pic will show the best what I want to say

Jabbar Guliyev
  • 13
  • 3

1 Answers1

0
SELECT SUBSTRING_INDEX(qurum1, ' ', 1) AS first_part,
SUBSTRING_INDEX(qurum1, ' ', -1) as second_part FROM qurum;
Rakesh Shewale
  • 497
  • 6
  • 22
  • ` SELECT qurum_id, qurum,qurum1, SUM(CASE WHEN DATE_FORMAT(aa.input_date, '%m') BETWEEN 01 AND 06 THEN 1 END) AS I, SUM(CASE WHEN DATE_FORMAT(aa.input_date, '%m') BETWEEN 07 AND 12 THEN 1 END) AS II, SUBSTRING_INDEX(qurum1, '|', 1) AS first_part, SUBSTRING_INDEX(qurum1, '|', -1) as second_part FROM qurum AS qur INNER JOIN (SELECT id, qurum1,input_date FROM DATA ) AS aa ON qur.qurum_id = aa.qurum1 WHERE DATE_FORMAT(aa.input_date, '%Y') = 2017 GROUP BY qurum_id ORDER BY qurum_id` Like this? – Jabbar Guliyev Apr 17 '17 at 13:49
  • http://chat.stackoverflow.com/rooms/141896/mysql-explode – Rakesh Shewale Apr 17 '17 at 14:00
  • I added one more digit to qurum1 (now it is 1|6|7) and the row disapperared – Jabbar Guliyev Apr 17 '17 at 14:01
  • I beg you please help – Jabbar Guliyev Apr 18 '17 at 04:42