#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    2
    Rep Power
    0

    MySQL Matching Across Multiple Records


    Hi,

    I'm having a bit of touble trying to match a string over many records, I can't find anything online, which makes me think I'm googleing for the wrong keyword or operator. I thought it would be a smiple query but can't get my head around it

    Take the following:

    SELECT DISTINCT Column1 FROM TableName WHERE Column1='test1' AND Column2='test2' AND Column3='';

    This is fine and and returns a list of Column1 values where Column3 is empty.

    But ... and here's where I get lost. Column values are not unique. There could be 10 records where:

    Column1 = test
    AND
    Column2 = test2


    But of thoes 10 maybe 3 where Coumn3 is blank.

    I only want to retun the value of Column1 if thoes 10 records are all blank.

    Does that make sense?

    Thanks in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,402
    Rep Power
    394
    Code:
    select Column1 
      from tableName 
     where Column1 = 'test1' 
       and Column2 = 'test2'
     group by column1
    having sum(case when column3 <> '' then 1 else 0 end) = 0
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    2
    Rep Power
    0
    Originally Posted by swampBoogie
    Code:
    having sum(case when column3 <> '' then 1 else 0 end) = 0
    That's brilliant!!!... makes perfect sense! Thanks

IMN logo majestic logo threadwatch logo seochat tools logo