How do I use MySQL Joins to select forum boards that users are allowed to read?

Question

I have three primary tables in this scenario: categories, boards, and permissions. The categories and boards tables are pretty self-explanatory. The permissions table contains rows that will determine whether or not the user is allowed to either read threads within a board, create threads within a board, reply to threads within a board, or moderate threads and posts within a board. In the table, the user's ability for each action are indicated by a 0(off, or false) or 1(on, or true). These are based on the member group that the user falls in.

What I would like to do is create a query that selects categories. It should only select categories that have boards that the user can read. Basically, if a category doesn't have any boards that the user can read, it won't be selected. If the category has even only one board that the user can read, it will be selected.

The next query is to select the boards within a certain category that a user can read. Based on the same principals as stated above. What's the point of displaying a board that a user cannot read only for them to find that out after they have already clicked the link?

I've never really created a complicated query like this on my own, so I wouldn't even know where to start. I would really appreciate it if you could help me out.

Update

So, I decided to give it a go, and this is what I came up with:

$query = "
            SELECT
                b.category_id, b.board_id, b.position,
                p.group_id, p.board_id, p.read
            FROM forum_boards AS b
                INNER JOIN forum_permissions AS p ON (
                        p.board_id = b.board_id,
                        p.group_id = 1,
                        p.read = 1
                )
            WHERE b.category_id = ".$category_id."
            ORDER BY b.position";

Note that this is the query to get boards within a certain category that the user can read. It is currently returning the error below, and I can't figure out why.

Operand should contain 1 column(s)

Answer 1

As Niko has already provide the example query using joins so i am providing the subquery version so that make some difference with the already provided answer

The fisrt subquery is what you ask for What I would like to do is create a query that selects categories. It should only select categories that have boards that the user can read. Basically, if a category doesn't have any boards that the user can read, it won't be selected. If the category has even only one board that the user can read

I have added the condition status=1 that your forum_boards are active and i just put the group_id=1 so you have to provide the right user group id

SELECT * FROM forum_categories WHERE category_id IN (
SELECT category_id FROM forum_boards WHERE `status`=1 AND board_id IN (
SELECT board_id FROM forum_permissions WHERE group_id=1 AND `read`=1) )

Here is your second query you ask for the same requirement but this time to select boards but within a category , again i added the condition status=1 set according to your needs and i don't know about the category_id so i just put it as category_id=1 you must take care of it to put the right category id

SELECT *  FROM forum_boards WHERE `status`=1 AND board_id IN (
SELECT board_id FROM forum_permissions WHERE group_id=1 AND `read`=1) AND category_id=1

Hope it makes sense