[FIXED] How to get record not joined result PostgreSQL

Issue

I have

table customer {ID(primary key-autoincrement),CUSTOMERNAME}
table item {ID (primary Key-autoincrement),PRODUCT,PRICE}
table price {ID (primary key-autoincrement),CUSTOMERID (foreign key),ITEMID (foreign-key),PRICE}

Now I am filling data like this:

table customer : {1,ABC},{2,DEF}
table item  :{1,EGG,50},{2,BRUSH,100},{3,SHOES,290},{4,SOCKS,120},{5,PILLOW,200}
table price :{1,1,3,320}

If I get the price for customer ABC (customer ID :1), then will be displayed result like this (5 records):

{1,1,EGG,50},{2,1,BRUSH,100},{3,1,SHOES,320},{4,1,SOCKS,120},{5,1,PILLOW,200}

But if I want to display price for customer DEF, then only displayed 4 records:

{1,1,EGG,50},{2,1,BRUSH,100},{4,1,SOCKS,120},{5,1,PILLOW,200}

How can I display the price for item with item ID (3) with default price 290 for customer DEF?

I use left outer join for this case then takes record with joined, then for second case they only returned 4 records.

Here my query (returned 4 records):

SELECT TMP."ID",TMP."IDCUSTOMER",TMI."NAME",
CASE WHEN TMP."PRICE" IS NULL THEN TMI."PRICE" ELSE TMP."PRICE" END AS "CUSTOMPRICE"
FROM mitem TMI 
LEFT OUTER JOIN mprice TMP ON TMP."IDITEM"=TMI."ID"
LEFT OUTER JOIN mcustomer TMC ON TMC."ID"=TMP."IDCUSTOMER"
WHERE TMP."IDCUSTOMER"='2' OR TMP."ID" IS NULL

Any idea beside without using UNION to join result?

Solution

Use CROSS JOIN.

Like this:

SELECT TMP."ID",TMP."IDCUSTOMER",TMI."NAME",
CASE WHEN TMP."PRICE" IS NULL THEN TMI."PRICE" ELSE TMP."PRICE" END AS "CUSTOMPRICE"
FROM mitem TMI 
CROSS JOIN mcustomer TMC
LEFT OUTER JOIN mprice TMP ON TMP."IDITEM"=TMI."ID"
AND TMP."IDCUSTOMER"= TMC."ID" AND TMP."IDCUSTOMER"= '2' 
WHERE TMC."ID" = '2'

Answered By - Nishant Gupta

Answer Checked By - David Marino (PHPFixing Volunteer)