Am I missing something about join tables?

[zensunni]

Say I have a database like so:

USER
user_id
user_name

REPORT
report_id
report_name

CATEGORY
category_id
category_name

USER_TO_REPORTS
user_id
report_id

REPORT_TO_CATEGORY
report_id
category_id

Now, If I wanted to pull out all the reports and their categories, I would have to make three database calls and two loops:

[code=mysql]
SELECT report_id FROM USER_TO_REPORTS where user_id = <user_id>
// For each report_id in the last record set:
SELECT category_id FROM REPORT_TO_CATEG ORY where report_id = <report_id>
// For each category_id in the last record set
SELECT name FROM CATEGORY where category_id = <category_id>
[/code]

Is this common practice, or is there something I should be doing in MySQL to make this less convoluted?

[Atli]

One question about your database.
Can each report be linked to many users and many categories?
If not, there is a much simpler way to do this.

Before I go on. Unless I am very much mistaken, the queries you posted would fetch the names of all categories that a specific user has reports in, not all reports and their category names.

And to answer you question.
You can use joins to link those three tables to create whatever output you want.
For example, to fetch a list of all categories a user has reports in:
[code=php]
SELECT DISTINCT c.name
FROM USER_TO_REPORTS AS u_r
INNER JOIN REPORT_TO_CATEG ORY as r_c
ON u_r.user_id = <user_id>
AND r_c.report_id = u_r.report_id
INNER JOIN CATEGORY AS c
ON r_c.category_id = c.category_id
[/code]

P.S. Didn't manage to test this... my host is down :(

[zensunni]

I'll develop my question a little better.

I'm looking to display all the categories with the reports listed underneath them. A report can be listed in multiple categories. So, a report might show up in more than one category list.

[zensunni]

The inner join you used wasn't exactly what I needed, but it put me on the right path. Thanks for the help :)