Hi,

> The following SQL statement is returning only those records which have
> a matching record in the right table.
>
> SELECT ST.sale_type_nm, MIN( SL.bill_no ), MAX( SL.bill_no )
> FROM sale_type ST LEFT JOIN sale SL ON ( ST.company_id = SL.company_id )
> AND ( ST.id = SL.s_type )
> WHERE ( SL.entry_date BETWEEN '2.5.05' AND '2.5.05' )
> GROUP BY ST.sale_type_nm
>
> What I want is all the record from the left table and null values from
> the right table which doesnt have matching record in the left table.

With making comparisons in the WHERE-clause you mostly turn a OUTER JOIN into an INNER JOIN (except
IS NULL...). Add the comparison to the ON-clause.

SELECT
ST.sale_type_nm,
MIN( SL.bill_no ),
MAX( SL.bill_no )
FROM
sale_type ST
LEFT JOIN sale SL ON
( ST.company_id = SL.company_id ) AND
( ST.id = SL.s_type ) and
( SL.entry_date BETWEEN '2.5.05' AND '2.5.05' )
GROUP BY
ST.sale_type_nm

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info