user2967888 user2967888 - 4 months ago 9
SQL Question

Mysql user subscription query fails

I have a database named socialnetwork and has 5 tables

category
,
post_category
,
posts
,
subscribe
,
user
.

my table structures

-------- -------------
category posts
-------- ------------
categoryID postID
categoryName post
userID
categoryID

-------------- ---------------
post_category subscribe
--------------- ---------------
postID subscriberID
categoryID categoryID

--------------
usertable
--------------
userID
userName


data's in the table

category table usertable
-------------------------- -------------------
categoryID categoryName userID userName
--------------------------- --------------------
1 film 1 jijojohn32
2 television 2 sijojohn


posts_category table subscribe table
------------------ -------------------------
postID categoryID subscriberID categoryID
--------------------- ------------------------
1 1 1 1
1 2 1 2
2 2 2 2


posts table
---------------------------------------------------
postID post userID categoryID
--------------------------------------------------
1 this post is cool 1 1
2 demo post 2 2


User 1 can subscribe to different categories and he can see the articles in the categories he subscribes. That's what i am trying to implement here. And i have this query but it's not giving me the result i want.

USE socialnetwork;
SELECT socialnetwork.usertable.userName,socialnetwork.posts.post, GROUP_CONCAT(socialnetwork.category.categoryName) as category
FROM socialnetwork.category
INNER JOIN subscribe
ON subscribe.categoryID = category.categoryID
INNER JOIN posts
ON posts.categoryID = subscribe.categoryID
INNER JOIN usertable
ON usertable.userID = posts.userID
INNER JOIN socialnetwork.post_category
ON post_category.postID = posts.postID

WHERE subscriberID = "1"
GROUP BY socialnetwork.category.categoryName


Here's the result i am getting

---------------------------------
username post category
----------------------------------
jijojohn32 this post is cool film, film
sijojohn demo post television


The result i want

---------------------------------------------
username post category
-------------------------------------------
jijojohn32 this post is cool film,television
sijojohn demo post television


I want the post from the categories he subscribed to , the username of the user posted the articles , and categories which posts reside. What's wrong in my query ?. any idea ?. thanks

Answer

Here's the working query. I made some modifications in the table. Deleted the categoryID from posts. Made a new table called post_category.

--------------
post_category
-------------
postID
categoryID

Here's the query

SELECT GROUP_CONCAT(category.categoryName) as category  , category.categoryID , subscribe.subscriberID , posts.post ,
  usertable.userName

from category

INNER JOIN subscribe
ON subscribe.categoryID = category.categoryID


INNER JOIN post_category
ON category.categoryID = post_category.categoryID

INNER JOIN posts
ON posts.postID =  post_category.postID

INNER JOIN usertable
ON usertable.userID = posts.userID

WHERE subscriberID = 1
GROUP BY post_category.postID
Comments