I have an issue with data.
id | filename
1 | john.jpg
2 | steve.jpg
id | client_id | asset_id
1 | 1 | 1
2 | 2 | 1
3 | 1 | 2
4 | 2 | 2
5 | 3 | 2
I need todo a query like:
SELECT * Asset_Image WHERE Asset_Client.client_id is 1,2
This should return both asset_id 1 and 2
However I need todo something like
SELECT * Asset_Image WHERE Asset_Client.client_id is 1,2,3
Which should only return Asset_Image with the id of 2
I can paste queries I've tried but I think they are rather silly and dont work so...note attachment_id = asset_id
SELECT * FROM Asset_Image as d WHERE `enabled` = 1 AND id in
(select attachment_id from (select ac.attachment_id, GROUP_CONCAT(ac.client_id ORDER BY ac.client_id) as con
from Asset_Client as ac where ac.enabled = 1 AND ac.attachment_type = 'image' GROUP BY ac.attachment_id HAVING con = '1,4') as filtered)
AND d.`status` != 3 ORDER BY d.id DESC LIMIT 0, 100
However If I query for client_id 1 and 2, I dont get asset id of 2
FROM ( SELECT asset_id
WHERE client_id IN ( <font color='"#0000FF"'>1,2,3</font> ) -- [B]3[/B] clients
HAVING COUNT(DISTINCT client_id) = <font color='"#0000FF"'>[B]3[/B]</font> ) AS x
ON Asset_Image.id = x.asset_id
Sorry for the delay,thank you very much! Works perfectly.
I might need to consider restructuring the tables, I'm not sure this is ideal performance wise.
This topic is now archived. It is frozen and cannot be changed in any way.