I have a 'blog' table, a 'categories' table and a 'blogcat_xref' table. I want to pull out all details on the blog plus any associated categories. Is the norm to do this in one query? I assume the less queries, the better so I wanted to give this a go and see if there is a clean solution.
A little info on the design of the xref table: The blogcat_xref contains a composite primary key consisting of blog_id and cat_id (the id for the blog, and the id for the category, respectively). If a blog contains more than one category, this x reference table will hold multiple rows with the same blog_id.
Here's how I'm pulling the blog details:
// query 1: the blog
$sql = 'SELECT a.blog_id, a.blog_date, a.blog_edit_date, a.blog_title, a.blog_body, a.blog_author, a.blog_editor, a.blog_status, c.first_name AS author_first, d.first_name AS editor_first FROM blog a LEFT JOIN users c ON a.blog_author = c.id LEFT JOIN users d ON a.blog_editor = d.id WHERE blog_status = 1';
This is pulling all published blog info (minus images, for now) except which category or categories the article belongs to. The second query that I'm using to pull category info is as follows:
Here's how I'm pulling the categories:
$sql = "SELECT CONCAT(a.cat_title, ' ') AS cat_group FROM categories a INNER JOIN blogcat_xref b USING (cat_id) WHERE b.blog_id = $blogId";
If the article is in 2 categories, say, for example, Category1 and Category 2, cat_group contains "Category1 and Category 2". I wouldn't mind formatting that a bit nicer, but that's a problem for another time.
The issue I see with doing it this way with two queries is if i'm loading 10 articles per page, the query running in the loop to pull the categories hits the database 10 times on a page load.
I have tried using UNION to join the two statements together but I end up with errors saying the two queries have a different number of columns. I've tried using a subquery but I end up with an error stating that the sub query contains multiple results (most articles belong to more than one category).
Is there a good way to pull all blog detail, including the categories, in one query? I appreciate any help on this.