SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Loading photos tagged to clients

    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:


    Code MySQL:
    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

    Code MySQL:
    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

    Code MySQL:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT Asset_Image.id
         , Asset_Image.filename 
      FROM ( SELECT asset_id
               FROM Asset_Client 
              WHERE client_id IN ( 1,2,3 ) -- 3 clients
             GROUP
                 BY asset_id
             HAVING COUNT(DISTINCT client_id) = 3 ) AS x
    INNER
      JOIN Asset_Image
        ON Asset_Image.id = x.asset_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •