SQL Statement (JOIN)

Question

I have the following MySql Table:

--------------------------------------------
Table 'category'
--------------------------------------------
category_id    name        parent_id
1              animal      NULL
2              vegetable   NULL
3              mineral     NULL
4              dog         1
6              cat         1
7              carrot      2
8              quartz      3

The following SQL Statement will list all results like this:

---

category_id    category    sub_category
4              animal      dog
6              animal      cat
7              vegetable   carrot
8              mineral     quartz

SELECT sub_category.category_id AS category_id,
       category.name AS category,
       sub_category.name AS sub_category
FROM category
LEFT OUTER JOIN category AS sub_category
    ON sub_category.parent_id = category.category_id
WHERE category.parent_id IS NULL
ORDER BY category_id

But now I have another table with categories that particular users are NOT interested in:

--------------------------------------------
Table 'category_filter'
--------------------------------------------
user_id   category_id
1         4
1         7
1         8

How can I make a query for one particular user so that I get a list of all categories with an info whether a user is interested or not?

Something like this for example:

--------------------------------------------------------------
category_id    category    sub_category    interested
4              animal      dog             NULL
6              animal      cat             6
7              vegetable   carrot          7
8              mineral     quartz          NULL

Thanks in advance for your support.

Answer 1

Looks like you just need to add another join to the query to include the filter:

SELECT sub_category.category_id AS category_id,
       category.name AS category,
       sub_category.name AS sub_category,
       filter.category_id AS interested
FROM category
LEFT OUTER JOIN category AS sub_category
    ON sub_category.parent_id = category.category_id
LEFT OUTER JOIN (SELECT * FROM category_filter WHERE user_id = @UserID) filter ON category.category_id = filter.category_id
WHERE category.parent_id IS NULL
ORDER BY category.category_id

This will only pull the "interest" information for the given user, joining it to the set of all categories.

Answer 2

Use:

   SELECT c.category_id,
          c.name AS category,
          subc.name AS sub_category,
          cf.category_id AS interested
     FROM CATEGORY c
LEFT JOIN CATEGORY subc ON subc.category_id = c.parent_id
                       AND subc.parent_id IS NULL
LEFT JOIN CATEGORY_FILTER cf ON cf.category_id IN (c.category_id, subc.category_id)
                            AND cf.user_id = ?
    WHERE c.parent_id IS NOT NULL

The join to CATEGORY_FILTER is different from @djacobson's answer, in that it joins the table to both instances of the CATEGORY table.