Re: Trying to get categories and relational table information in one query

[Steven B]

Thanks, That helps. Getting closer anyhow.I might be missing something about the way the tables are joined upon themselves.

For my purposes, I try:

SELECT blog_categories.id, blog_post2cat.id AS linkId, COALESCE(blog_post2cat.idPost = 47,0) AS linked

FROM blog_categories LEFT JOIN blog_post2cat

ON blog_categories.id = blog_post2cat.idCat

ORDER BY blog_categories.id

For output, I get:

+---------+--------+--------+
| id      | linkId | linked |
+---------+--------+--------+
|       1 |      8 |      0 |
|       1 |      7 |      1 |
|       2 |   NULL |      0 |
|       3 |      9 |      1 |
+---------+--------+--------+

But I want the categories table to only show one result for each record and a 1 if there is a link and 0 if there isn't. I'm looking for this output.

+---------+--------+--------+
| id      | linkId | linked |
+---------+--------+--------+
|       1 |      7 |      1 |
|       2 |   NULL |      0 |
|       3 |      9 |      1 |
+---------+--------+--------+

Realistically I don't care about the relational tables id. That is just for testing, so my actual goal is this output:

+---------+--------+
| id      | linked |
+---------+--------+
|       1 |      1 |
|       2 |      0 |
|       3 |      1 |
+---------+--------+

Thanks for your help.



Edited 1 time(s). Last edit at 05/31/2012 02:48AM by Steven B.