--- In firebird-support@yahoogroups.com, "majstoru" <majstoru@...> wrote:

>
> Hi,
>
> I have a SQL statement on 3 tables! When one of table in this
> statement have no record (return) null, I get a result of SQL
> statement all fields null!!! How to set SQL to get fields which is not
> null?
>
> Here is the statent:
>
> SELECT T1.Price, T1.Qty, T2.CPrice, T3.Pricex
> FROM Table1 T1, Table2 T2, Table3 T3
> WHERE T1.id = T2.Article AND T3.Article = T2.Article AND T2.Article =
> :ARTICLE
>
> Thanks
>

The behaviour you observe is the exact correct behaviour as per SQL
standard. Where no match is found in one of your where clauses, it
will return false, and the following logic will always return false.

(?? AND ?? AND FALSE)

You are using the SQL 89 join syntax, move it forward 3 years to a
more readable syntax.

SELECT T1.Price, T1.Qty, T2.CPrice, T3.Pricex
FROM Table1 T1
join Table2 T2 on (T1.id = T2.Article)
join Table3 T3 on (T3.Article = T2.Article)
WHERE T2.Article = :ARTICLE;

Join (aka inner join) means link these tables on these fields where
the following conditions match. Breaking it down a little,

FROM Table1 T1
join Table2 T2 on (T1.id = T2.Article)

means join together Table1 and Table2 where the ID field in Table1
matches the Article field in Table2. Inner join means that you do not
want to include records from Table1 which have no match in Table2, and
you do not want to include records from Table2 which have no match in
Table 1.

This is why outer joins were invented.

Left Join means include the table on the left (Table1) even if there
are no matching records in the right table (Table2). Right Join is
just the opposite. Full outer join includes records from both tables
that have no match in the other.

Note that in the above query, you are putting a where clause on
T2.ArticleID. This will block out records where table2 is not equal to
the parameter (this includes null values). If you want to include
nulls, you will need to decide whether you want left, right, full
outer or inner joins for each table. You may also need to put 'OR
T2.Article IS NULL' in your where clause depending on your conditions.

Note that outer joins restrict an optimisers choice of path through
the query. You need to be careful you don't force it to do unnecessary
table scans. In otherwords, use join unless you need to have the nulls
as well.

Adam