Mysql View with conditional column

Question

This must be easier than I am making it, but I am going crazy here. What I want to do is create a view from three tables, like such:

SELECT tableA.name, tableB.id, tableB.categoryID, tableC.categoryParentID
FROM tableB, tableA where tableA.id=tableB.id JOIN tableC on
tableC.categoryID = tableB.categoryID;

Simple enough. However, what I want to accomplish is that in the case where tableB.categoryID is NULL, I want tableC.categoryParentID to show up as a NULL value rather than as the categoryParentID value.

So far with my results I either can get the table to show all the rows where tableB.categoryID IS not NULL, and exclude the rows that have a null value for categoryID entirely - I don't want to do this - OR I get some weird variation that includes every row for categoryParentID from tableC for every row from tableB - which gives me upwards of 3,000,000 rows, and is also incorrect.

If this is unclear, I can explain more.

Thanks in advance.

It sounds like you want a LEFT JOIN instead of an INNER JOIN — Taryn, Sep 04 '12 at 00:03

score 1 · Accepted Answer · answered Sep 04 '12 at 00:02

1

If I'm understanding you correctly, what you want is:

SELECT tableA.name,
       tableB.id,
       tableB.categoryID,
       tableC.categoryParentID
  FROM tableA
  JOIN tableB
    ON tableB.id = tableA.id
  LEFT
 OUTER
  JOIN tableC
    ON tableC.categoryID = tableB.categoryID
;

(See the "left outer join" section of the Wikipedia article on SQL joins.)

answered Sep 04 '12 at 00:02

ruakh

175,680
26
273
307

That worked perfectly! Thanks. I'll try the wiki article...for some reason trying to understand JOIN syntax always make my eyes glaze over a little. – dgo Sep 04 '12 at 00:12
deleted - as no long relevant – dgo Sep 04 '12 at 00:49

Mysql View with conditional column

1 Answers1