Re: SQL Syntax

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks for your help,
I am coding SQL statements for some time but I never came across an in depth
SQL language book, please let me know if you know of one

Shmuel


"Adam Machanic" <amachanic@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23Y3A$7CrFHA.904@xxxxxxxxxxxxxxxxxxxxxxx
> You're close as is. Try:
>
> SELECT
> TableA.FieldA,
> TableB.FieldA
> FROM TableA
> LEFT OUTER JOIN TableB ON TableA.PK = TableB.PK
> AND TableB.FieldX = xyz
>
>
> Note that you'll have to replace 'PK' with the actual primary key. Also
> note that I've put the condition into the ON clause. When working with
> OUTER JOINs, it's important to remember that any condition in the ON
> clause
> of the LEFT OUTER JOIN will be logically treated as part of the join, and
> if
> it evaluates to false will cause the columns for that table to return NULL
> for any row in the 'left' table. If that same condition was in the WHERE
> clause, it would be logically evaluated AFTER the JOIN -- and therefore
> would filter the final result set, and, in this case, produce a result set
> that would be equivalent to that produced by an INNER JOIN. You should
> play
> with this a bit, if you have time, and get a feel for what I'm talking
> about.
>
> Let me know if you have any further questions.
>
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>
> "S Shulman" <smshulman@xxxxxxxxxxx> wrote in message
> news:uxFS5OCrFHA.2996@xxxxxxxxxxxxxxxxxxxxxxx
>> Hi
>>
>> I want to get records fron the db based on the following criteria:
>> TableA.FieldA, TableB.FieldA FROM TableA
>> LEFT OUTER JOIN TableB etc.
>> WHERE (TableB.FieldX =xyz or in case there in no records in TableB so
> these
>> fieldswill be Null)
>>
>> Is there any way to achieve that?
>> Thank you in advance,
>> Shmuel Shulman
>>
>>
>
>


.

Relevant Pages

  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Re: Better "Join" vs "Where" clause?
    ... There is an extreme case, famous, in MS SQL Server only though, and the ... example is in BOL I think, where it uses a full outer join to make what is ... you can even have a ON clause on a pure constant. ... ever done comparing equivalent implicit and explicit joins with Jet ...
    (microsoft.public.access.queries)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... That means that your filter, c.custid = "AB1234", is applied after all rows of the customer table have been LEFT OUTER JOINed to the my_view VIEW with the results stored into a temp table. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: Performance degradation with view and "left outer join" vs. "from x, outer y"
    ... "SQL Turned Every Which Way But Loose", I reminded the attendees that the ANSI SQL '92 parsing rules REQUIRE that filters in the WHERE clause of a query MUST be processed POST-JOIN in order for an RDBMS to be compliant. ... Even if I were to bypass the Hibernate generated SQL and roll my own, that did not work at all, it returned all rows unfiltered. ... Now if I use the informix extended outer join syntax: ...
    (comp.databases.informix)
  • Re: How to query with both JOIN and aliases in Access?
    ... Access SQL recognizes Inner Join, LEFT JOIN, and RIGHT JOIN. ... It does not recognize LEFT OUTER JOIN and JOIN. ... LEFT JOIN (SELECT empID, max(datum) as LastDayWorked ... This query performs in SQL Server without any problem. ...
    (microsoft.public.access.queries)