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

    Join Date
    Jan 2012
    Posts
    18
    Rep Power
    0

    A challenge for SQL experts - I can't get query right


    Please can anyone help with the SQL syntax for this query?
    I've got part way there with a series of subqueries but cannot get the CodeLetter right.

    I need to extract the Name, CodeLetter and Value from the example table below where one (no more) of the values for A, B or C for a Name is less than 5

    ie
    a)The code is either A, B or C
    b)and the value is less than 5
    c)and the number of A,B,C values less than 5 for that person = 1

    So in the example table,
    Ben is selected as out of A, B and C, he has only B < 5
    Jim is not selected as both his A and C values < 5
    Eve is not selected as she has all of her A, B, C values > 5

    (I'm actually doing this in SQLite but MySQL is close enough)


    Name---CodeLetter---Value
    Jim--------A-----------4
    Jim--------B-----------7
    Jim--------C-----------3
    Jim--------D-----------6
    Jim ...

    Ben--------A----------6
    Ben--------B----------4
    Ben--------C----------7
    Ben--------E----------6
    Ben ...

    Eve--------A----------6
    Eve--------B----------7
    Eve--------C----------8
    Eve--------F----------2
    Eve ...

  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,143
    Rep Power
    4274
    Code:
    SELECT example.name
         , example.codeletter 
         , example.value
      FROM ( SELECT name
               FROM example
              WHERE codeletter IN ('A','B','C')
                AND value < 5
             GROUP
                 BY name
             HAVING COUNT(*) = 1 ) AS these
    INNER
      JOIN example
        ON example.name = these.name
     WHERE example.codeletter IN ('A','B','C')
       AND example.value < 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    18
    Rep Power
    0
    Brilliant Rudy thank you very much.
    I translated your sql to the real table and got the rows I wanted, then added the extra joins I needed to get my full dataset and it works like a charm.
    Regards

    PS
    I did check out your book but I'm afraid it's rather pricey in the UK as the international shipping seems to cost as much as the book itself.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,143
    Rep Power
    4274
    Originally Posted by dochsm
    PS
    I did check out your book but I'm afraid it's rather pricey in the UK as the international shipping seems to cost as much as the book itself.
    i was under the impression that sitepoint hasn't sold the dead tree version for a couple years, just the ebook

    i just looked it up on amazon.co.uk, and it sells for 55 pounds?!! wtf?? [insert picture of me gobsmacked]

    sorry, mate, you might just have to get the ebook
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo