Loading photos tagged to clients

Databases
1

Hello all,

I have an issue with data.

Asset_Image:
id | filename

1 | john.jpg
2 | steve.jpg

Asset_Client
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

2 

SELECT Asset_Image.id
     , Asset_Image.filename
  FROM ( SELECT asset_id
           FROM Asset_Client
          WHERE client_id IN ( [COLOR="#0000FF"]1,2,3[/COLOR] ) -- [B]3[/B] clients
         GROUP
             BY asset_id
         HAVING COUNT(DISTINCT client_id) = [COLOR="#0000FF"][B]3[/B][/COLOR] ) AS x
INNER
  JOIN Asset_Image
    ON Asset_Image.id = x.asset_id
3

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.