0

I have a table with folowing structure

CREATE TABLE `ABC` (
    `SNO` int(11) NOT NULL,<br>
    `ID1` int(11) unsigned NOT NULL DEFAULT '0',
    `ID2` int(11) unsigned NOT NULL DEFAULT '0',
    `TYPE` char(1) NOT NULL DEFAULT '',
    `TIME` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`SNO`),
 UNIQUE KEY `IND1` (`ID1`,`ID2`),
 KEY `RECEIVER` (`ID2`)
)

I want to get ID2's grouped by ID1's which have a certain TYPE 'A' .If no ID1 ID2 combination have TYPE 'A' then get all other ID1 ID2 combinations. I have used the following query-:

SELECT 
   ID1, 
   GROUP_CONCAT( ID2 ) , 
   GROUP_CONCAT( TYPE ) 
FROM CONTACTS
  WHERE TIME >  '2017-08-11 00:00:00'
  GROUP BY ID1

Now this gives me TYPE as a group concatenated string which i have to operate upon to get those ID1,ID2 combinations with TYPE 'A'.

Output-:

ID1    |  group_concat(ID2) | group_concat(TYPE) |
144111 | 556,3577,254389    |  A,I,I             |

Can there be a way to get this done with Mysql itself without the need of further group_concatenated string?

Would using temporary tables be a better way?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
ankit shukla
  • 187
  • 1
  • 7
  • Try to create temp table and store data's temporary table in your same query.. – vishal Aug 18 '17 at 13:12
  • Can you also post your expected result based on the output that you have provided? Maybe this [Split Comma Separated Row](https://stackoverflow.com/questions/19073500/sql-split-comma-separated-row) thread might help please read it. – Learning Aug 18 '17 at 22:55

0 Answers0