How to use IN clause from Sub query contains a group concat value

[kartisathis]

***********************************************
select * from table_name where RIGHT_CODE IN (SELECT GROUP_CONCAT('\'',`RIGHT_CODE','\'')as right_code
FROM table_name GROUP BY `GRP_CODE` , `ROOT_DESC`
HAVING count( `RIGHT_CODE` ) >=2
****************************************************

Actually the above query returns empty result.
But the query Inside IN cluase which returns ('1','28')..
If i directly pass the value inside IN clause means it returns a result,
for example:
select * from table_name where RIGHT_CODE IN ('1','28');

Is there any other way to overcome this issue please let me know..

[smantscheff]

Get rid of the quote marks.

select 1 in (concat("'","1","'")); 
-- result: false
select 1 in (concat("'1'")); 
-- result: false
select (1 in ('1'));
-- result: true

And you don't need the group_concat neither:

select * from table_name where RIGHT_CODE IN 
(SELECT RIGHT_CODE 
 FROM table_name 
 GROUP BY `GRP_CODE` , `ROOT_DESC`
 HAVING count( `RIGHT_CODE` ) >=2
);

[kartisathis]

Tanks Mr.smantscheff,
I tried as u said but the query returns only single row . I didnt get what i need ..
example:
If i use this,

SELECT * FROM table_name WHERE RIGHT_CODE IN
    (SELECT RIGHT_CODE
    FROM table_name
    GROUP BY `GRP_CODE` , `ROOT_DESC`
    HAVING count( `RIGHT_CODE` ) >=2
    );

It returns only one row that contains RIGHT_CODE=1
example :

SELECT * FROM table_name WHERE RIGHT_CODE IN (1);

But the actual result while i use group_concat means i got RIGHT_CODE=1,28

SELECT group_concat(RIGHT_CODE)
    FROM table_name
    GROUP BY `GRP_CODE` , `ROOT_DESC`
    HAVING count( `RIGHT_CODE` ) >=2;

i think u understood what my need is

[smantscheff]

Show some test data.
For using the IN clause you definitely don't need the group_concat function.

[kartisathis]

hi..
here i hook sample data for your reference,

CREATE TABLE `test` (
 `RIGHT_CODE` int(11) DEFAULT NULL,
 `GRP_CODE` int(11) DEFAULT NULL,
 `ROOT_DESC` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1


INSERT INTO `test` (`RIGHT_CODE`, `GRP_CODE`, `ROOT_DESC`) VALUES
(1, 1, '000001#'),
(2, 1,  '000001#000001#'),
(3, 1,  '000001#000002#'),
(4, 1, '000001#000003#'),
(5, 1, '000001#000004#'),
(6, 1, '000002#'),
(7, 1, '000002#000001#'),
(27, 1,  '000005#000001#'),
(28, 1, '000001#'),
(28, 2,  '000001#000001#');

I sure u know the result of this query ,

SELECT group_concat(RIGHT_CODE)
    FROM table_name
    GROUP BY `GRP_CODE` , `ROOT_DESC`
    HAVING count( `RIGHT_CODE` ) >=2;

from the above query i got 1,28..
if i pass this query inside IN clause mean i got only empty result ..
And i dont know why group concated value not working in IN clause???

[smantscheff]

If you mix aggregate and non-aggregate functions in MySQL you get invalid results.
What you want is either:

select * from test
where (grp_code, root_desc) in
(select grp_code, root_desc from test group by grp_code, root_desc having count(grp_code) > 1)

+------------+----------+-----------+
| RIGHT_CODE | GRP_CODE | ROOT_DESC |
+------------+----------+-----------+
| 1 | 1 | 000001# |
| 28 | 1 | 000001# |
+------------+----------+-----------+

or

select * from test
where right_code in 
(select right_code from test
where (grp_code, root_desc) in
(select grp_code, root_desc from test group by grp_code, root_desc having count(grp_code) > 1)
);

+------------+----------+----------------+
| RIGHT_CODE | GRP_CODE | ROOT_DESC |
+------------+----------+----------------+
| 1 | 1 | 000001# |
| 28 | 1 | 000001# |
| 28 | 2 | 000001#000001# |
+------------+----------+----------------+

[kartisathis]

Thank you so much for spending valueable time for me Mr.smantscheff,
The 1st method is what i need ...
Thank you so much and will you please add me as a friend.