Re: Newbie SQL Left Outer Join

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 02/20/05

  • Next message: shall_at_uaex.edu: "Re: Newbie SQL Left Outer Join" 
    Date: 20 Feb 2005 09:58:40 -0800

    Possibly this is what you want:

    SELECT R.name, M.item
     FROM MyReference R
     LEFT OUTER JOIN MyList M
     ON R.idkey=M.idkey
      AND M.fiscalyear=2005 And M.item=5
     WHERE R.fiscalyear=2005

    If you reference the unpreserved table (MyList) in the WHERE clause as
    you did then you effectively turn the OUTER join into an INNER join. 

    -- 
David Portas 
SQL Server MVP 
--
  • Next message: shall_at_uaex.edu: "Re: Newbie SQL Left Outer Join"

    Relevant Pages

    • Re: whats wrong with my syntax?
      ... any reference have a list of command that can be used for access and VB?? ... ie inner join, outer join, left outer join etc ... have Help system - see Microsoft Jet SQL Reference. ...
      (microsoft.public.vb.database)
    • Re: whats wrong with my syntax?
      ... in oracle, it can use "join" ... any reference have a list of command that can be used for access and VB?? ... ie inner join, outer join, left outer join etc ...
      (microsoft.public.vb.database)
    • Re: Compare two date into the same table
      ... FROM pres LEFT JOIN pres AS pres_1 ... the WHERE clause, keep in mind that due to the nature of the outer join, ... Remove the WHERE clause to convince ... > a reference date and the previous one... ...
      (microsoft.public.access.queries)
    • Re: Right Outer Joins and Entering Data into query result
      ... Are you joining on the primary key, ... I have a query that returns all the fields from this table and shows blank ... both tables but I when I run the query I then want to the reference number ... The two tables have a right outer join of this reference number. ...
      (microsoft.public.access.queries)
    • Re: SQL Syntax
      ... >> note that I've put the condition into the ON clause. ... >> of the LEFT OUTER JOIN will be logically treated as part of the join, ... >> SQL Server MVP ... >>> LEFT OUTER JOIN TableB etc. ...
      (microsoft.public.sqlserver.datamining)