Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Trying to select rows that aren't there.

    SELECT food.description, C nutrient_data.nutrient_value, P nutrient_data.nutrient_value, F nutrient_data.nutrient_value
    FROM food, nutrient_data as C, nutrient_data as P, nutrient_data as F
    WHERE (
    food.food_id = C.food_id AND C.nutrient_id = '1'
    ) AND (
    food.food_id = F.food_id AND F.nutrient_id = '2'
    ) AND (
    food.food_id = P.food_id AND P.nutrient_id = '3'
    ) AND food.food_id = '12';


    Ok... I know this is probably REALLY badly formed SQL Sorry, I'm not that good at it.

    It works if values for nutrient_id 1, 2 and 3 are present, otherwise it wont return anything. I know this is what you would expect, but I want it to return something if one of the nutrient's aren't present. I would use an OR but then I get hundreds of different results.

    What I need is for it to return 0 for a value for a nutrient if it isn't present rather than simply not returning any rows at all. That way, I can still use the other values that are there. Can this be done? Or am I just being confusing and ridiculous?

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select a.columnA, NVL(b.columnB, 0)
    from tableA a
    LEFT OUTER JOIN
    tableB b ON
    a.id = b.id;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    Problem with that is the value wont be "null". It simply wont exist, ie there wont be a value. I need it to be set to 0 if the value isn't present whilst still returning the other values or simply use the value if it is there.

    Hard to explain

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select food.description
         
    coalesce(C.nutrient_value,0) as C_nutrient_value
         
    coalesce(P.nutrient_value,0) as P_nutrient_value
         
    coalesce(F.nutrient_value,0) as F_nutrient_value
      from food
    left outer
      join nutrient_data 
    as C
        on food
    .food_id C.food_id 
       
    and C.nutrient_id '1' 
    left outer
      join nutrient_data 
    as P
        on food
    .food_id F.food_id 
        
    anD F.nutrient_id '2' 
    left outer
      join nutrient_data 
    as F
        on food
    .food_id P.food_id 
       
    and P.nutrient_id '3' 
     
    where food.food_id '12' 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by Chromate
    Problem with that is the value wont be "null". It simply wont exist, ie there wont be a value. I need it to be set to 0 if the value isn't present whilst still returning the other values or simply use the value if it is there.

    Hard to explain

    That is the definition of NULL - when a field does not have a value in it.

  6. #6
    Join Date
    Feb 2004
    Posts
    4
    That's what I thought, but I'm using MySQL, and...

    http://www.mysql.com/doc/en/Problems_with_NULL.html


  7. #7
    Join Date
    Feb 2004
    Posts
    4
    r937, Thanks SO much! That works a treat! You had the P's and F's mixed up, but I'll forgive you for that Thanks again.

Posting Permissions

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