Damir Tesanovic wrote:

> I tryed with
> SELECT A.barcode, B.barcode
> FROM shops A LEFT JOIN shops B
> ON (A.barcode = > B.barcode)
> WHERE A.shop_id = 1 AND B.shop_id = 2;

The problem is in the WHERE clause. The statement
results are generated as if the statement were executed
in this order:

First, all records from record stream A are selected.

Second, all records from record stream B that match the
criteria in the ON condition are selected and paired with
the corresponding A records.

Third, that record stream is filtered by the WHERE clause.

Your problem is that the WHERE clause references a field from
record stream B "AND B.shop_id = 2". That clause eliminates
all records in the combined stream that have no matching value
from stream B - null is not the same as 2.

SELECT A.barcode, B.barcode
FROM shops A LEFT JOIN shops B
ON (A.barcode = > B.barcode A
AND B.shop_id = 2)
WHERE A.shop_id = 1;

Note that the query may not actually be executed this way -
horribly inefficient - but the result set will be the same
as if the query had run this way.


Regards,


Ann