SQL: Multiple table query help

This site is best viewed in a modern browser with JavaScript enabled.

SQL: Multiple table query help

DeadlySin3

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.

Weedpacket

DeadlySin3 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.

That is indeed a code smell.

DeadlySin3 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.

Since of course they are two completely different queries that return different types of information.

Consider

SELECT
	blog.blog_id,
	blog.blog_date,
	blog.blog_edit_date,
	blog.blog_title,
	blog.blog_body,
	blog.blog_author,
	blog.blog_editor,
	blog.blog_status,
	author.first_name AS author_first,
	editor.first_name AS editor_first,
	CONCAT(cat.cat_title, ' ') AS cat_group
FROM
	blog
		LEFT JOIN users author ON blog.blog_author = author.id
		LEFT JOIN users editor ON blog.blog_editor = editor.id,
	categories cat
		INNER JOIN blogcat_xref bx WHERE bx.blog_id = blog.blog_id
WHERE blog_status = 1
GROUP BY blog.blog_id

And yes, you can write queries so that they span more than one line like that. Makes for easier debugging and reading.

(I've tried to make some guesses about the table structure based on the given description. Not sure it will work. Another possibility is to write the SELECT query to get the cat_titles and concatenate them inside the column list of the main SELECT:

SELECT blog.blog_id, 
	...
	editor.first_name AS editor_first,
	(SELECT CONCAT(cat.cat_title, ' ') FROM categories ... ) as cat_group
FROM
	blog
...

)

rithurawat

Thank you so much for sharing the code

DeadlySin3

Weedpacket,

You are the man! I appreciate the effort in helping me out. The examples that you provided gave me a clear path to follow. I have written several - very similar - queries like this. The main issue I was having was leaving out the GROUP BY blog_id portion leaving me with duplicate rows. Awesome work here!

DeadlySin3

I wanted to come back and just share the query that I think is going to work for me. I'm sure I'll end up editing this a bit more as I go along but this query pulls everything that I need for now relating blog articles to their respective categories, author, and editor.

# b prefix for blog, u for user, e for editor, c for category
$sql = 'SELECT
    b.blog_date,
    b.blog_edit_date,
    b.blog_title,
    u.first_name AS author_first,
    e.first_name AS editor_first,
    GROUP_CONCAT(c.cat_title) AS cat_group,
    GROUP_CONCAT(c.cat_id) AS catid_group,
    b.image_id,
    b.header_id,
    b.blog_body,
    b.blog_author,
    b.blog_editor
FROM
    blog b
LEFT JOIN users u ON
    b.blog_author = u.id
LEFT JOIN users e ON
    b.blog_editor = e.id,
    categories c
INNER JOIN blogcat_xref bx ON
    bx.cat_id = c.cat_id
WHERE
    bx.blog_id = b.blog_id AND b.blog_status = 1
GROUP BY
    b.blog_id';

Thanks again for the help. I've been coming to these forums for over a decade and I've got to say that this community is invaluable.