MySQL JOIN - & Get all the data from the right table even if there is no match advertisements

I have two tables, one with data (A) and other with categories (B) which is in one of those categories or uncategorized. I need to make simple stats, so I am using JOIN to get all data from table A and join it with table B.

What I need is, that even if there is no match in A, I want to show all categories from B on graph (with count = 0, but I need them to be shown).

So if there are no items with that category id, it does not show that category at all, but I need to show it, even if it has 0 items.

I tried LEFT JOIN, RIGHT JOIN, both of them with UNION and nothing worked.

Table A

id | some | irrelevant | data | category
-----------------------------------------
1  |      |            |      | 0
2  |      |            |      | 0
3  |      |            |      | 1
4  |      |            |      | 2
5  |      |            |      | 3
6  |      |            |      | 3
. . . . . . . . . . .

Table B

id | title  | color
---------------------
1  | Cat 1  | #FFFFFF
2  | Cat 2  | #FF00FF
3  | Cat 3  | #FF0000
4  | Cat 4  | #00FF00
5  | Cat 5  | #00FFFF
6  | Cat 6  | #000000
7  | Cat 7  | #FFFF00

With those data I want to get something like:

Array ( [category] => 0 [count] => 3 [title] => [color] => )
Array ( [category] => 1 [count] => 1 [title] => Cat 1 [color] => #FFFFFF )
Array ( [category] => 2 [count] => 1 [title] => Cat 2 [color] => #FF00FF )
Array ( [category] => 3 [count] => 2 [title] => Cat 3 [color] => #FF0000 )
Array ( [category] => 4 [count] => 0 [title] => Cat 4 [color] => #00FF00 )
Array ( [category] => 5 [count] => 0 [title] => Cat 5 [color] => #00FFFF )
Array ( [category] => 6 [count] => 0 [title] => Cat 6 [color] => #000000 )
Array ( [category] => 7 [count] => 0 [title] => Cat 7 [color] => #FFFF00 )

But thats what I actually get:

Array ( [category] => 0 [count] => 3 [title] => [color] => )
Array ( [category] => 1 [count] => 1 [title] => Cat 1 [color] => #FFFFFF )
Array ( [category] => 2 [count] => 1 [title] => Cat 2 [color] => #FF00FF )
Array ( [category] => 3 [count] => 2 [title] => Cat 3 [color] => #FF0000 )

This is the call:

"SELECT category, COUNT(*) AS count, title, color FROM A LEFT JOIN B ON B.id=A.category GROUP BY category"

PS.: Once more, I trued RIGHT join too and UNION of RIGHT and LEFT join. Also, the names of tables are really long, cant really use them in call as A.category etc.

Is there some way to do that without using two SQL calls and two loops? I really don't want to do that.

Thanks.

---

And what about this :

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE TableA
    (`id` int, `category` int)
;

INSERT INTO TableA
    (`id`, `category`)
VALUES
    (1, 0),
    (2, 0),
    (3, 1),
    (4, 2),
    (5, 3),
    (6, 3)
;

CREATE TABLE TableB
    (`id` int, `title` varchar(5), `color` varchar(7))
;

INSERT INTO TableB
    (`id`, `title`, `color`)
VALUES
    (1, 'Cat 1', '#FFFFFF'),
    (2, 'Cat 2', '#FF00FF'),
    (3, 'Cat 3', '#FF0000'),
    (4, 'Cat 4', '#00FF00'),
    (5, 'Cat 5', '#00FFFF'),
    (6, 'Cat 6', '#000000'),
    (7, 'Cat 7', '#FFFF00')
;

Query 1:

SELECT TableB.id as category, count(distinct TableA.id) as count,title,color
FROM
(SELECT * FROM TableB
UNION
SELECT 0 as id, '' as title, '' as color) AS TableB
LEFT OUTER JOIN TableA on TableB.id = TableA.category
GROUP BY TableB.id, title, color
ORDER BY TableB.id

Results:

| CATEGORY | COUNT | TITLE |   COLOR |
|----------|-------|-------|---------|
|        0 |     2 |       |         |
|        1 |     1 | Cat 1 | #FFFFFF |
|        2 |     1 | Cat 2 | #FF00FF |
|        3 |     2 | Cat 3 | #FF0000 |
|        4 |     0 | Cat 4 | #00FF00 |
|        5 |     0 | Cat 5 | #00FFFF |
|        6 |     0 | Cat 6 | #000000 |
|        7 |     0 | Cat 7 | #FFFF00 |

Also, instead of using UNION SELECT 0 as id, '' as title, '' as color, you can add a category with id 0 in your TableB