Can we use FIND_IN_SET() function for multiple column in same table

Question

NOTE : I tried many SF solution, but none work for me. This is bit challenging for, any help will be appreciated.

Below is my SQL-Fiddle link : http://sqlfiddle.com/#!9/6daa20/9

I have tables below:

CREATE TABLE `tbl_pay_chat` (
nId int(11) NOT NULL AUTO_INCREMENT,
npayid int(11) NOT NULL,
nSender int(11) NOT NULL,
nTos varchar(255) binary DEFAULT NULL,
nCcs varchar(255) binary DEFAULT NULL,
sMailBody varchar(500) binary DEFAULT NULL,
PRIMARY KEY (nId)
)
ENGINE = INNODB,
CHARACTER SET utf8,
COLLATE utf8_bin;

INSERT INTO tbl_pay_chat
(nId,npayid,nSender,nTos,nCcs,sMailBody)
 VALUES
(0,1,66,'3,10','98,133,10053','Hi this test maail'),
(0,1,66,'3,10','98,133,10053','test mail received');

 _____________________________________________________________

CREATE TABLE `tbl_emp` (
empid int(11) NOT NULL,
fullname varchar(45) NOT NULL,
PRIMARY KEY (empid)
)
ENGINE = INNODB,
CHARACTER SET utf8,
COLLATE utf8_bin;



INSERT INTO `tbl_emp` (empid,fullname)
VALUES
(3, 'Rio'),
(10, 'Christ'),
(66, 'Jack'),
(98, 'Jude'),
(133, 'Mike'),
(10053, 'James');

What I want :

1. JOIN above two tables to get fullname in (nTos & nCcs) columns.

2. Also, I want total COUNT() of rows.

What I tried is below query but getting multiples time FULLNAME in 'nTos and nCcs column' also please suggest to find proper number of row count.

 SELECT a.nId, a.npayid, e1.fullname AS nSender, sMailBody, GROUP_CONCAT(b.fullname ORDER BY b.empid) 
 AS nTos, GROUP_CONCAT(e.fullname ORDER BY e.empid) AS nCcs
 FROM    tbl_pay_chat a
    INNER JOIN tbl_emp b
        ON FIND_IN_SET(b.empid, a.nTos) > 0
    INNER JOIN tbl_emp e 
        ON FIND_IN_SET(e.empid, a.nCcs) > 0
    JOIN tbl_emp e1
        ON e1.empid = a.nSender
 GROUP   BY a.nId ORDER BY a.nId DESC;

I hope I made my point clear. Please help.

Answer 1

You have a horrible data model. You should not be storing lists of ids in strings. Why? Here are some reasons:

• Numbers should be stored as numbers not strings.
• Relationships between tables should be declared using foreign key relationships.
• SQL has pretty poor string manipulation capabilities.
• The use of functions and type conversion in ON often prevents the use of indexes.

No doubt there are other good reasons. Your data model should be using properly declared junction tables for the n-m relationships.

That said, sometimes we are stuck with other people's really, really, really, really bad design decisions. There are some ways around this. I think the query that you want can be expressed as:

 SELECT pc.nId, pc.npayid, s_e.fullname AS nSender, pc.sMailBody,
        GROUP_CONCAT(DISTINCT to_e.fullname ORDER BY to_e.empid) 
 AS nTos,
        GROUP_CONCAT(DISTINCT cc_e.fullname ORDER BY cc_e.empid) AS nCcs
 FROM tbl_pay_chat pc INNER JOIN
      tbl_emp to_e
      ON FIND_IN_SET(to_e.empid, pc.nTos) > 0 INNER JOIN
      tbl_emp cc_e
      ON FIND_IN_SET(cc_e.empid, pc.nCcs) > 0 JOIN
      tbl_emp s_e
      ON s_e.empid = pc.nSender
 GROUP BY pc.nId
 ORDER BY pc.nId DESC;

Here is a db<>fiddle.