SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql left join (working on local environment and not on server)

    Hello,

    I'm having problems with LEFT JOIN with the following tables:

    table domains:
    id domain priority filter

    table entries:
    id domain url title


    I want to join those by domain where filter is not TRUE (where is it false or NULL - in case we dont have a suitable join row):

    SELECT * FROM entries LEFT JOIN domains USING (domain) WHERE filter != 1 ORDER BY priority DESC

    This syntax will work just fine on my local environment but when I excute it on my web server it will always return 0 results although it shouldnt.

    It looks like mysql cant compare NULL columns of the joined table.

    Does anyone have any idea what might be going on?

    MySQL client version on server: 5.0.81

    Many thanks in advance!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    one of these should do the trick... let me know which one
    Code:
    SELECT * 
      FROM entries 
    LEFT OUTER
      JOIN domains 
        ON domains.domain = entries.domain
     WHERE COALESCE(domains.filter,0) = 0
    ORDER 
        BY domains.priority DESC
    Code:
    SELECT entries.* 
      FROM entries 
    LEFT OUTER
      JOIN domains 
        ON domains.domain = entries.domain
       AND domains.filter = 1
     WHERE domains.domain IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem is I want to include NULL results as well in the query
    Where filter can be 0/1 or NULL (in case domain for that row doesnt exist), so it should return all rows where filter equals 0 or NULL (where its not 1)

  4. #4
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great, the first one seems to do the trick.

    Would never thought of that trick with COALESCE

    Thanks a lot!

  5. #5
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by l2u View Post
    Great, the first one seems to do the trick.

    Would never thought of that trick with COALESCE

    Thanks a lot!
    Alternatively there is IFNULL() method for dealing with Mysql null.
    see this link for reference:
    http://dev.mysql.com/doc/refman/5.0/...with-null.html

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    IFNULL is actually the same as COALESCE, but with only two arguments, whereas COALESCE can have any number of arguments

    but IFNULL is proprietary to MySQL, whereas COALESCE is standard SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    IFNULL is actually the same as COALESCE, but with only two arguments, whereas COALESCE can have any number of arguments

    but IFNULL is proprietary to MySQL, whereas COALESCE is standard SQL
    So we can use COALESCE blindly instead of IFNULL, right?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    blindly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    blindly?
    I mean to say: without looking here and there , without googling, without posting in forums, we can use COALESCE instead of IFNULL in COALESCE vs IFNULL 's battle.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if you put it that way, yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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