I have two tables in MySQL.
First table is EMPLOYEES, it contain such columns as LASTNAME, NAME, MIDDLENAME, BIRHDATE and others about employees.
Second table is PROJECTS. It has among other things column STAFF. STAFF has rows like lastname1 name1, middlename1; lastname2 name2, middlename2.....
I need to get people from EMPLOYEES who are in the STAFF.
query
SELECT LASTNAME, NAME, MIDDENAME from EMPLOYEES where
CONCAT('%', LASTNAME, ' ', NAME, ' ',MIDDENAME, '%')
like (SELECT STAFF FROM PROJECTS)
doesn't work because LIKE must have one substring from 2nd query
and query
SELECT LASTNAME, NAME, MIDDENAME from EMPLOYEES where
CONCAT('%', LASTNAME, ' ', NAME, ' ',MIDDENAME, '%')
IN (SELECT STAFF FROM PROJECTS)
doesn't work too because for IN need full matching
any ideas?
That's what happens when you have delimited string like that in table which is against normalization. First consider normalizing your table. For now, you can perform a JOIN probably like below but if STAFF column has data in exact in format else matching will not occur.
SELECT e.LASTNAME, e.NAME, e.MIDDENAME
FROM EMPLOYEES e
JOIN PROJECTS p
ON CONCAT(e.LASTNAME, ' ', e.NAME, ' ', e.MIDDENAME) = p.STAFF;
You can as well use FIND_IN_SET() function like below but again consider changing your DB design first and Normalize your table(s)
SELECT LASTNAME,
NAME,
MIDDENAME
FROM (
SELECT LASTNAME, NAME,
MIDDENAME,
CONCAT(e.LASTNAME, ' ', e.NAME, ' ', e.MIDDENAME) as Full_Name
FROM EMPLOYEES) e
JOIN PROJECTS p
ON FIND_IN_SET(Full_Name, p.STAFF) > 0;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments