Help with a query using nested queries
I am needing some help, I think to all you MySQL gurus out there it'll be very easy. So here goes:
I have a table(A) that has a string (123:456:789) which are IDs of work sectors(in table B). I am trying to get most details from table A and creating a string of work sectors from table B using REPLACE(work_sector_ids, ':',',') and using this in a nested query (SELECT GROUP_CONCAT(work_sector_description) FROM table B WHERE ID IN(REPLACE(work_sector_ids, ':', ',').
Full query:
SELECT ths.subcon_company AS 'Company',
CONCAT(ths.subby_id, '-', ths.unique_ref) AS 'Contractor Ref',
(REPLACE(ths.work_sector, ':', ',')) AS 'Work Sector String',
(SELECT GROUP_CONCAT(description) FROM conf_subcontractors_sectors WHERE `id` IN (REPLACE(ths.work_sector, ':', ',')))
FROM trans_hdr_subcontractors ths
WHERE `client` = 0000
I'm sorry if there isn't enough info.