SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast jR³'s Avatar
    Join Date
    Sep 2003
    Location
    belgium
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Left join not working

    Hello,
    I need to do something like this:
    <snippet from mysql docs: http://dev.mysql.com/doc/refman/5.0/en/join.html>

    Code:
    SELECT table1.* FROM table1
      LEFT JOIN table2 ON table1.id=table2.id
      WHERE table2.id IS NULL;
    This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL.

    </snippet>

    I'm working on a multi- language site,
    and I need to select all rows from one language that are not present in another language..
    lang_id ( is the language id f.e. en/ fr / nl)
    language_key( is the key of the translated part )
    languages
    Code:
    SELECT l.language_id,l.language_key FROM languages l
    left JOIN languages l2 on l.language_key=l2.language_key 
    where l2.language_key IS NULL AND l.language_id='en'
    AND l2.language_id='nl'
    So in this query I select all the translated parts that are present in the English language but need to be translated to Dutch.

    Thanks in advance!

    jr3

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by jR³
    This assumes that table2.id is declared NOT NULL.
    actually, it doesn't matter if it is or not



    as for your query, it follows the same pattern, but one of the conditions must go into the ON clause:
    Code:
    select l.language_id
         , l.language_key 
      from languages l
    left 
      join languages l2 
        on l2.language_key = l.language_key 
       and l2.language_id = 'nl'
     where l.language_id = 'en'
       and l2.language_key is null
    r937.com | rudy.ca | 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
  •