Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    44

    Unanswered: help on a join query

    Hi, I have 2 tables table1 & table2::

    TABLE1:
    ________________
    INDEX....VALUE

    1............Apple
    2............Banana
    3............Peaches
    4............Orange
    ----------------

    TABLE2:
    ________________
    INDEX....COLOR

    1...........Red
    2...........Yellow
    ----------------

    Now, I want to give the user a view of fruits with their respective colors. For this I want to give the user 3 options based on color:
    1)show fruits with RED color
    2)show fruits with Yellow color
    3)show fruits with any color

    I tried writing the following query:

    select A.VALUE,B.COLOR
    from TABLE1 as A
    LEFT JOIN
    TABLE2 as B
    ON A.INDEX=B.INDEX
    WHERE B.COLOR LIKE <variable>

    my <variable> is assigned the value 1)'Red' for the first scenario,'yellow' for the 2nd & '%' for the 3rd. But with '%' I only get the following output:

    ________________
    VALUE.....COLOR

    Apple.......Red
    Banana....Yellow
    -----------------

    While this is understandable why I am getting this output, can anybody help me in correcting the query to get the following o/p for the 3rd scenario:

    _________________
    VALUE.....COLOR

    Apple..... Red
    Banana....Yellow
    Peaches
    Orange
    -----------------

    would really appreciate any help,
    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change the keyword WHERE to the keyword AND

    the condition on color belongs in the ON clause of the join, not the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    NULL values cannot be compared to strings, so when you test to see if NULL is LIKE '%' the answer is NULL!
    Code:
    SELECT a.value
         , b.value As colour
    FROM   @table1 As a
     LEFT
      JOIN @table2 As b
        ON b.id = a.id
    WHERE  Coalesce(b.value, 'NO COLOUR MATCH HERE!') LIKE @variable
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    the condition on color belongs in the ON clause of the join, not the WHERE clause
    Are you sure about that one Rudy?

    If I search for "RED" I do not want the NULLs returned.
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2009
    Posts
    44
    Quote Originally Posted by gvee
    NULL values cannot be compared to strings, so when you test to see if NULL is LIKE '%' the answer is NULL!
    Code:
    SELECT a.value
         , b.value As colour
    FROM   @table1 As a
     LEFT
      JOIN @table2 As b
        ON b.id = a.id
    WHERE  Coalesce(b.value, 'NO COLOUR MATCH HERE!') LIKE @variable
    thanks a ton george, that solved it. Another query by the way regarding the Coalesce command, I understand that its a kind of compressed IF ELSE construct. The syntax for Coalesce is everywhere mentioned as :

    Coalesce(expr1,expr2,expr3..n) & is mentioned that it can take any number of arguments. But its failing for a single argument. Are 2 arguments the minimum??

    thanks.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Failing?

    Coalesce can take oodles of parameters and returns the first non-null value
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2009
    Posts
    44
    I removed the second parameter from Coalesce(...) and tried...

    SQL0440N No authorized routine named "COALESCE" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

    SQL0440N No authorized routine named "COALESCE" of type "FUNCTION
    Is it confusing it with a user defined function??
    I am using DB2 by the way

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rocker86
    I am using DB2 by the way
    maybe DB2 doesn't support all ANSI SQL functions?

    you did post in the ANSI SQL forum, you know
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2009
    Posts
    44
    Quote Originally Posted by r937
    maybe DB2 doesn't support all ANSI SQL functions?

    you did post in the ANSI SQL forum, you know
    Well I thought my query was a pretty generic SQL syntactical one so posted it here.
    Nevertheless , maybe DB2 isnt allowing the single argument Coalesce function (works fine for multiple arguments in DB2).

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    of what use would a single argument COALESCE be?

    if foo is not NULL, then COALESCE(foo) will return foo

    if foo is NULL, then COALESCE(foo) will return NULL

    why would you use COALESCE(foo) when foo by itself gives the same result?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    SQL-2003 (ISO/IEC 9075-2:2003 (E)):
    6.11 <case expression>
    [...]
    <case abbreviation> ::=
    NULLIF <left paren> <value expression> <comma> <value expression> <right paren>
    | COALESCE <left paren> <value expression> { <comma> <value expression> }... <right paren>
    [...]


    I.e. two arguments or more.

Posting Permissions

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