[RESOLVED] return default value after LEFT JOIN

Hey all,
Suppose I have this...

Code:

---

SELECT a.x, a.y, a.z,
            b.x, b.y, b.z
FROM tableA a
LEFT JOIN tableB b ON a.x=b.x AND b.w="somevalue"

---

This should return all rows from tableA and for those rows where a.x=b.x AND b.w="somevalue" the rows will be appended with b.x, b.y and b.z

Is there a way to set default values the b.x, b.y and b.z for those rows that did not meet the join condition?

Right now I get null for those values, and it would really make life simpler if they had default values.
thanks
kevin

depends on the database. sql server would be

Code:

---

SELECT a.x, a.y, a.z,
            case when b.x is null then 'default' else b.x end
FROM tableA a
LEFT JOIN tableB b ON a.x=b.x AND b.w="somevalue"

---

best is to check for primary key is null so that in case you have rows in table b where a.x=b.x and b.w="somevalue" but b.y is null you truely get null for these.

thanks for that, I'm using MYSQL (should have said so in the OP).

I found the IFNULL keyword which works...

Code:

---

SELECT a.x, a.y, a.z,
            IFNULL(b.x,'bxDefault') as bX,
            IFNULL(b.y,'byDefault') as bY,
            IFNULL(b.z,'bzDefault') as bZ,

FROM tableA a
LEFT JOIN tableB b ON a.x=b.x AND b.w="somevalue"

---

Quote:

---

Code:

---

SELECT a.x, a.y, a.z,
            IFNULL(b.x,'bxDefault') as bX,
            IFNULL(b.y,'byDefault') as bY,
            IFNULL(b.z,'bzDefault') as bZ,

FROM tableA a
LEFT JOIN tableB b ON a.x=b.x AND b.w="somevalue"

---

---

yes, there are many ways to check for NULL. What i also wanted to point out is that with your code above you may get the default value even though there is a record in b. Assuming b.x i s the primary key the following would only return default if there is no row:

Code:

---

SELECT a.x, a.y, a.z,
            (CASE WHEN b.x IS NULL THEN 'bxDefault' ELSE b.x END) as bX,
            (CASE WHEN b.x IS NULL THEN 'byDefault' ELSE b.y END) as bY,
            (CASE WHEN b.x IS NULL THEN 'bzDefault' ELSE b.z END) as bZ
FROM tableA a
LEFT JOIN tableB b ON a.x=b.x AND b.w="somevalue"

---

you see the difference? but if you do not have any NULLs in b.y and b.z fields then it does not matter.

I do see the difference, and thanks for the added information.

It's not so much whether there are null in TableB before the query (I know there are not). I really need to make sure the resulting table does not have nulls when a row from TableB is not returned from the join which the IFNULL seems to do.
kevin