0

I have this table:

// mytable
+----+---------+---------+
| id | related |  color  |
+----+---------+---------+
| 1  | NULL    | red     |
| 2  | 1       | blue    |
| 3  | NULL    | green   |
| 4  | 1       | white   |
| 5  | 3       | brown   |
| 6  | NULL    | gray    |
| 7  | 3       | black   |
| 8  | 1       | orange  |
| 9  | 6       | pink    |
+----+---------+---------+

I have an id number and I need to get the color of related id.

Here is some examples:

$id = 4; // I need to get `red`
$id = 5; // I need to get `green`
$id = 6; // I need to get `gray`
$id = 9; // I need to get `gray`

And here is my query:

SELECT t2.color FROM mytable t1 JOIN mytable t2 ON t1.related = t2.id WHERE t1.id = :id

It works for almost all rows and it returns expected color. Just it doesn't work for those rows which have NULL related. How can I fix the problem?

stack
  • 10,280
  • 19
  • 65
  • 117

2 Answers2

2

You can use a LEFT JOIN to associate the related colors to the id in the related column. In cases where there is no related color, the COALESCE() will return the primary color.

SELECT t.id, COALESCE(r.color,t.color) AS color
FROM myTable t
LEFT OUTER JOIN myTable r ON r.id = t.related
WHERE t.id = :id

Here is the INNER JOIN query for comparison. You'll see that you lose the rows where the related id is NULL, which I don't believe is what you want.

SELECT t.id, COALESCE(r.color,t.color) AS color
FROM myTable t
INNER JOIN myTable r ON r.id = t.related
WHERE t.id = :id
devlin carnate
  • 8,309
  • 7
  • 48
  • 82
  • Ah, I understand `ISNULL()`. But why `LEFT OUTER JOIN`? What's wring with `JOIN`? – stack May 25 '16 at 22:28
  • Also your query doesn't what `WHERE` clause ..! Why? – stack May 25 '16 at 22:30
  • the syntax for left join doesn't need a where clause unless you're specifying a particular subset. This will get all rows. – Jhecht May 25 '16 at 22:31
  • How does it not work. And the reason for left outer is in the event that the equijoin between id and related doesn't exist. – T Gray May 25 '16 at 22:46
  • You need `COALESCE` instead of `ISNULL`. Or `IFNULL` – Paul Spiegel May 25 '16 at 22:47
  • @PaulSpiegel `ISNULL()` Besides, that `LEFT JOIN` returns all rows. I just need one row and one column. In other word I just need to get one record containing a color name. – stack May 25 '16 at 22:49
  • @PaulSpiegel You are right .. `ISNULL` isn't correct and it should be replace with either `IFNULL` of `COALESCE` ..! But still result isn't what I need ... – stack May 25 '16 at 22:56
  • My last question. what's the difference between `LEFT OUTER JOIN` and `LEFT JOIN` ? – stack May 25 '16 at 23:26
  • @stack - [it's the same thing](http://stackoverflow.com/questions/3183669/difference-between-join-and-outer-join-in-mysql). I've always preferred the explicit `LEFT OUTER JOIN` syntax. – devlin carnate May 26 '16 at 02:52
  • @devlincarnate I see, thank you .. just as a note, you were right, I need a `LEFT` not `INNER` join. – stack May 26 '16 at 11:31
1
SELECT c1.id as id,
  IFNULL(c2.color, c1.color) as color
FROM colors c1 
LEFT OUTER JOIN colors c2
  ON c1.related = c2.id;

Check this sqlfiddle with a demo.

Gustavo Rubio
  • 10,209
  • 8
  • 39
  • 57
  • Thank you .. upvote, just what's `OUTER` ? Why not just `LEFT JOIN`? – stack May 26 '16 at 00:10
  • 1
    @stack In the case of mysql I think there is no difference, IIRC its an ANSI-SQL standard convention but you can omit it. A simple left join would do. The most common join is an "INNER" join where you want records that intersect given a key, so think of an OUTER as an opposite of that. Now, you still want all records from the LEFT regardless so you are basically saying "take everything on the LEFT and try to match it with the right, if there is no coincidence for a record, leave the OUTER record as null but still return the LEFT. – Gustavo Rubio May 26 '16 at 01:36