CODEWITHSUNDEEP
postgresqlleft-join
Andrew
Andrew

Reputation: 12009

Query join result appears to be incorrect

I have no idea what's going on here. Maybe I've been staring at this code for too long.

The query I have is as follows:

CREATE VIEW v_sku_best_before AS
  SELECT
    sw.sku_id,
    sw.sku_warehouse_id  "A",
    sbb.sku_warehouse_id "B",
    sbb.best_before,
    sbb.quantity
  FROM SKU_WAREHOUSE sw
    LEFT OUTER JOIN SKU_BEST_BEFORE sbb
      ON sbb.sku_warehouse_id = sw.warehouse_id
  ORDER BY sbb.best_before

I can post the table definitions if that helps, but I'm not sure it will. Suffice to say that SKU_WAREHOUSE.sku_warehouse_id is an identity column, and SKU_BEST_BEFORE.sku_warehouse_id is a child that uses that identity as a foreign key.

Here's the result when I run the query:

+--------+-----+----+-------------+----------+
| sku_id |  A  | B  | best_before | quantity |
+--------+-----+----+-------------+----------+
|  20251 | 643 | 11 | <<null>>    |      140 |
+--------+-----+----+-------------+----------+
(1 row)

The join specifies that the sku_warehouse_id columns have to be equal, but when I pull the ID from each table (labelled as A and B) they're different.

What am I doing wrong?

Upvotes: 0

Views: 28

Answers (1)

Nerdwood
Nerdwood

Reputation: 4022

Perhaps just sw.sku_warehouse_id instead of sw.warehouse_id?

Upvotes: 1

Related Questions