Is there any way to have two different where clause in one select procedure in Interbase firebird?

Question

Is there any way to have two different where clause in one select procedure in Interbase firebird?

I created two tables that will support this question. The desired output is that the select procedure will display all data from table SAMPLE_SINGLE even if there is no SINGLE_PK present in table SAMPLE_DOUBLE.

CREATE TABLE SAMPLE_SINGLE (   
  SINGLE_PK SMALLINT NOT NULL,   
  SINGLE_NAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  SINGLE_AMOUNT SMALLINT,   
  SINGLE_QUANTITY SMALLINT);

CREATE TABLE SAMPLE_DOUBLE (
  DOUBLE_PK SMALLINT NOT NULL,
  SINGLE_PK SMALLINT,
  DOUBLE_QUANTITY SMALLINT);


CREATE PROCEDURE SELECT_FROM2TABLES
RETURNS(
  SINGLE_NAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
  SINGLE_AMOUNT SMALLINT,
  SINGLE_QUANTITY SMALLINT,
  TOTAL_DOUBLE_QUANTITY SMALLINT,
  REMAINING_QUANTITY SMALLINT)
AS
BEGIN
  FOR
    SELECT
      A.SINGLE_NAME,
      A.SINGLE_AMOUNT,
      A.SINGLE_QUANTITY,
      SUM(B.DOUBLE_QUANTITY),
      A.SINGLE_QUANTITY - SUM(B.DOUBLE_QUANTITY)

    FROM SAMPLE_SINGLE A, SAMPLE_DOUBLE B
     WHERE A.SINGLE_PK = B.SINGLE_PK

    GROUP BY
     A.SINGLE_NAME,
      A.SINGLE_AMOUNT,
      A.SINGLE_QUANTITY

    INTO
      :SINGLE_NAME,
      :SINGLE_AMOUNT,
      :SINGLE_QUANTITY,
      :TOTAL_DOUBLE_QUANTITY,
      :REMAINING_QUANTITY
  DO
    BEGIN
      SUSPEND;
    END
END;

For this select procedure it will only display data from table SAMPLE_SINGLE that has a SINGLE_PK present in table SAMPLE_DOUBLE because of the

FROM SAMPLE_SINGLE A, SAMPLE_DOUBLE B
         WHERE A.SINGLE_PK = B.SINGLE_PK

I want also to display data from Table A that is not present in table B.

Here is the Sample Data,

Table A (SAMPLE_SINGLE) SINGLE_PK  SINGLE_NAME SINGLE_AMOUNT SINGLE_QUNATITY
                          1          asdf          100            5
                          2          qwer          50             7
Table B (SAMPLE_DOUBLE) DOUBLE_PK  SINGLE_PK DOUBLE_QUANTITY
                          1            1           3 

My desired output in a select procedure,

SINGLE_NAME SINGLE_AMOUNT SINGLE_QUANTITY TOTAL_DOUBLE_QUANTITY RMAINING_QUANTITY
    asdf          100            5               3                   2
    qwer          50             7               0                   7

Here is the actual result for the above procedure, because of the WHERE A.SINGLE_PK = B.SINGLE_PK, it will only display the first row

SINGLE_NAME SINGLE_AMOUNT SINGLE_QUANTITY TOTAL_DOUBLE_QUANTITY RMAINING_QUANTITY
    asdf          100            5               3                   2

Answer 1

The problem is that you use an implicit (SQL-89 style) join, and the equality in the where will automatically exclude those rows where there is no row in B. Instead you need to use the explicit (SQL-92 style) join, specifically a left outer join:

A LEFT outer join includes all the records from the left set, but only matching records from the right set.

So use:

FROM SAMPLE_SINGLE A
LEFT OUTER JOIN SAMPLE_DOUBLE B ON A.SINGLE_PK = B.SINGLE_PK

See also Joins in the Firebird 2.5 Language Reference.