CODEWITHSUNDEEP
phpmysqlsqldatabasecodeigniter
Nathan Stanford II
Nathan Stanford II

Reputation: 617

Query to show all blog posts while linking in categories

So I have a query that should get all posts from a table while linking the posts categories and the user that created the post. What I am getting though is just a single post returned, not all posts. Below is the schema:

Posts
=====
id

Categories
==========
id

Post categories
===============
postID
categoryID

And here is the SQL code I have so far, kinda deep but it gets all categories concatenated into a single field.

SELECT
    blgpostcategories.*,
    blgcategories.id,
    GROUP_CONCAT(blgcategories.name) AS categories,
    blgposts.*,
    users.firstName,
    users.id AS usersId,
    users.lastName,
    users.email
FROM blgposts
RIGHT OUTER JOIN blgpostcategories
    ON blgposts.id = blgpostcategories.postID
RIGHT OUTER JOIN blgcategories
    ON blgpostcategories.categoryID = blgcategories.id
INNER JOIN users
    ON blgposts.userID = users.id

UPDATED Query from JNK - Still only returning a single row :-(

SELECT
  blgpostcategories.*,
  blgcategories.id,
  GROUP_CONCAT(blgcategories.name) AS categories,
  blgposts.*
FROM blgposts

LEFT OUTER JOIN blgpostcategories
  ON blgposts.id = blgpostcategories.postID
LEFT OUTER JOIN blgcategories
  ON blgpostcategories.categoryID = blgcategories.id

Upvotes: 0

Views: 185

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52675

In an answer by Adam Robinson to a similar question

Because you're using an aggregate in your query (GROUP_CONCAT), your query is being grouped. Since you have no group by clause, your group is the entire result set (hence seeing every tag the author has used). Because MySQL allows for using non-grouped columns in grouped statements, you aren't getting an error, but you aren't getting the query that you want.

In order to retrieve the proper results, you need to group your query on thread.id.

In your case just adding GROUP BY blgcategories.id should do it

Upvotes: 1

JNK
JNK

Reputation: 65187

Two things I see right off the bat:

1 - Do a LEFT OUTER JOIN not a RIGHT. RIGHT means "show me all the stuff in the right table, whether or not I have anything matching in the left table." You want everything from blogposts so do a left.

2 - Your INNER JOIN may be an issue as well. Are you sure users is populated fully?

EDIT:

The issue is you are using an aggregate function without a GROUP BY! Take out the GROUP_CONCAT() and it should work fine.

Upvotes: 0

Related Questions