Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts

    exclusion from normalized table query

    Code:
    id_galery   color
    
    1             blue
    1             red
    1             green
    
    2             blue
    2             red

    this would return id_galery 1 and 2, exactly what I want, tested.
    Code:
    select id_galery,count(id_galery),  
    from galery_optional_inf 
    where color in ('blue','red') 
    group by id_galery 
    having count(color) = 2
    How to exclude 1 from resultset as it contains green ?
    Last edited by BubikolRamios; 11-10-2011 at 07:54 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    ??? Easy:

    Code:
    SELECT G1.id_galery, G1.theCount
    FROM 
        ( select id_galery,count(*) AS theCount  
          from galery_optional_inf 
          where color in ('blue','red') 
          group by id_galery 
          having count(*) = 2 ) AS G1
    LEFT JOIN galery_optional_inf AS G2
    ON G1.id_galery = G2.id_galery AND G2.color = 'green' 
    WHERE G2.id_galery IS NULL
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    BubikolRamios (11-10-2011)

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Thanks.

    I was imagining that there could be one litle simple sql (-:

    with out any joins.

    I imagine red part would work, or not ? Not I think.

    Code:
    SELECT G1.id_galery, G1.theCount
    FROM 
        ( select id_galery,count(*) AS theCount  
          from galery_optional_inf 
          where color in ('blue','red') 
          group by id_galery 
          having count(*) = 2 ) AS G1
    LEFT JOIN galery_optional_inf AS G2
    ON G1.id_galery = G2.id_galery AND G2.color in ('green','purple',...) 
    WHERE G2.id_galery IS NULL
    EDIT: As far as I tested, this should work.
    Last edited by BubikolRamios; 11-10-2011 at 08:42 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Yes, it should work fine.

    You could also do
    Code:
        ... AND G2.color NOT IN ('blue','red')
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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