Jump to content

Archived

This topic is now archived and is closed to further replies.

adamjnz

Querying Multiple Tables

Recommended Posts

Hey Guys,

I downloaded a tutorial on joining MySQL tables in a query from DMXZone however what I am trying to do goes slightly past the scope of that tutorial. I have tried playing around with the SQL query I want but cannot get it to work. If someone has some insight into this it would be much appreciated.

The query as I was trying it:
[code]SELECT BlogPostID, ImgSrc, PostTitle, PostText, DateTime, Category, Deleted, User.Name, COUNT(`BlogPostComment.BlogCommentID`) AS `CommentCount`
FROM BlogPost JOIN User USING (userID) JOIN BlogPostComment USING (BlogPostID)
WHERE BlogPost.Deleted = '0'[/code]

Essentially what I am trying to do is this:
1) Pull the blog post data out of the 'BlogPost' Table
2) Crossreference the UserID stored in the 'BlogPost' table to pull out the Full Name of the Author from the 'User' Table
3) Count the number of comments for that particular blog post from the 'BlogPostComment' table using the BlogPostID


I can get 1 and 2 working togeather fine, but when I try and add in step 3 it all goes pear shaped (including telling me that DateTime is not ambiguous  ???)

Share this post


Link to post
Share on other sites
UPDATE:

After fiddling with the code I can finally get it to run something without errors, however there are 2 problems:(

1) It will only return a Name from User.Name for the first result
2) The comment count is an aggregate for all Blog Posts, where I want it to be for each Blog Post

Any ideas?

[code]SELECT BlogPost.BlogPostID, ImgSrc, PostTitle, PostText, BlogPost.DateTime, Category, Deleted, User.Name, COUNT(BlogPostComment.BlogCommentID) AS CommentCount
FROM BlogPost JOIN User USING (userID) RIGHT JOIN BlogPostComment ON BlogPostComment.BlogPostID = BlogPost.BlogPostID
WHERE BlogPost.Deleted = '0'
GROUP BY BlogPostID[/code]

Share this post


Link to post
Share on other sites
Very strange looking query... I'm not sure what you mean by (1); as for (2), you have what looks like the right GROUP BY statement, so I'm confused.

Share this post


Link to post
Share on other sites
[quote author=fenway link=topic=111020.msg449898#msg449898 date=1160494244]
Very strange looking query... I'm not sure what you mean by (1); as for (2), you have what looks like the right GROUP BY statement, so I'm confused.
[/quote]

Ok, so for 1) it should be returning the Name of the person who wrote the post, this is got by taking the UserID and looking it up in the User table and returning the associated username. However, it is only returning a result for the 1st record returned by the query, all other results this feild returns nothing.

2) This is working but it is giving me the total for all blog posts, not just the blog post the comments are related to.

Hope this makes sense

Share this post


Link to post
Share on other sites
1) Probably because the JOIN condition is failing, so you're getting NULLs for the users.* columns.

2) I'm not sure how that could be, unless you're getting back a strange result set.

Share this post


Link to post
Share on other sites
I will try and put it in better context, here is what I am trying to do:

1)       Pull BlogPostID, ImgSrc, PostTitle, PostText, BlogPost.DateTime, Category, Deleted values from the BlogPost table

2)       Pull the Name value from the User table based on the UserID stored against the record in the BlogPost table.

3)       Calculate the number of comments linked to a BlogPost from the BlogPostComment table based on the BlogPostID stored in the BlogPost table.

The following query works fine for points 1 & 2 but when I try and add point 3 into the mix with the 2nd query it does the following to the results:

1)       Adds up the total comments for all blog posts and return the value to each record returned

2)       Stop returning the Name for the User table for all but the first record returned.

[u][b]Points 1 & 2:[/b][/u]

SELECT BlogPost.BlogPostID, ImgSrc, PostTitle, PostText, BlogPost.DateTime, Category, Deleted, User.Name
FROM BlogPost JOIN User USING (userID)
WHERE BlogPost.Deleted = '0'

[b]Returns something like:[/b]

[table]
  [tr]
    [td]Record [/td]
    [td]BlogPostID [/td]
    [td]ImgSrc [/td]
    [td]PostTitle [/td]
    [td]PostText [/td]
    [td]DateTime [/td]
    [td]Category [/td]
    [td]Deleted [/td]
    [td]Name [/td]
  [/tr]
  [tr]
    [td]1 [/td]
    [td]00001 [/td]
    [td]Post1.jpg [/td]
    [td]Title 1 [/td]
    [td]Text 1… [/td]
    [td]2006-10-10…. [/td]
    [td]Other [/td]
    [td]0 [/td]
    [td]Adam Jobbins [/td]
  [/tr]
  [tr]
    [td]2 [/td]
    [td]00002 [/td]
    [td]Post2.jpg [/td]
    [td]Title 2 [/td]
    [td]Text 2… [/td]
    [td]2006-10-10…. [/td]
    [td]Announcements [/td]
    [td]0 [/td]
    [td]Test User 2 [/td]
  [/tr]
  [tr]
    [td]N [/td]
    [td]0000N [/td]
    [td]PostN.jpg [/td]
    [td]Title N [/td]
    [td]Text N… [/td]
    [td]2006-10.. [/td]
    [td]Whatever [/td]
    [td]0 [/td]
    [td]Users Name [/td]
  [/tr]
[/table]

[u][b]Points 1, 2 & 3:[/b][/u]

SELECT BlogPost.BlogPostID, ImgSrc, PostTitle, PostText, BlogPost.DateTime, Category, Deleted, User.Name, COUNT(BlogPostComment.BlogCommentID) AS CommentCount
FROM BlogPost JOIN User USING (userID) RIGHT JOIN BlogPostComment ON BlogPost.BlogPostID = BlogPostComment.BlogPostID
WHERE BlogPost.Deleted = '0'
GROUP BY BlogPostID

[b]Returns something like: (2 Records Total in the BlogPostComment table, both linked to BlogPostID = 00001)[/b]

[table]
  [tr]
    [td]Record [/td]
    [td]BlogPostID [/td]
    [td]ImgSrc [/td]
    [td]PostTitle [/td]
    [td]PostText [/td]
    [td]DateTime [/td]
    [td]Category [/td]
    [td]Deleted [/td]
    [td]Name [/td]
    [td]CommentCount [/td]
  [/tr]
  [tr]
    [td]1 [/td]
    [td]00001 [/td]
    [td]Post1.jpg [/td]
    [td]Title 1 [/td]
    [td]Text 1… [/td]
    [td]2006-10-10…. [/td]
    [td]Other [/td]
    [td]0 [/td]
    [td]Adam Jobbins [/td]
    [td]2 [/td]
  [/tr]
  [tr]
    [td]2 [/td]
    [td]00002 [/td]
    [td]Post2.jpg [/td]
    [td]Title 2 [/td]
    [td]Text 2… [/td]
    [td]2006-10-10…. [/td]
    [td]Announcements [/td]
    [td]0 [/td]
    [td][/td]
    [td]2 [/td]
  [/tr]
  [tr]
    [td]N [/td]
    [td]0000N [/td]
    [td]PostN.jpg [/td]
    [td]Title N [/td]
    [td]Text N… [/td]
    [td]2006-10.. [/td]
    [td]Whatever [/td]
    [td]0 [/td]
    [td][/td]
    [td]2 [/td]
  [/tr]
[/table]

[b]SHOULD RETURN:[/b]

[table]
  [tr]
    [td]Record [/td]
    [td]BlogPostID [/td]
    [td]ImgSrc [/td]
    [td]PostTitle [/td]
    [td]PostText [/td]
    [td]DateTime [/td]
    [td]Category [/td]
    [td]Deleted [/td]
    [td]Name [/td]
    [td]CommentCount [/td]
  [/tr]
  [tr]
    [td]1 [/td]
    [td]00001 [/td]
    [td]Post1.jpg [/td]
    [td]Title 1 [/td]
    [td]Text 1… [/td]
    [td]2006-10-10…. [/td]
    [td]Other [/td]
    [td]0 [/td]
    [td]Adam Jobbins [/td]
    [td]2 [/td]
  [/tr]
  [tr]
    [td]2 [/td]
    [td]00002 [/td]
    [td]Post2.jpg [/td]
    [td]Title 2 [/td]
    [td]Text 2… [/td]
    [td]2006-10-10…. [/td]
    [td]Announcements [/td]
    [td]0 [/td]
    [td]Test User 2 [/td]
    [td]0 [/td]
  [/tr]
  [tr]
    [td]N [/td]
    [td]0000N [/td]
    [td]PostN.jpg [/td]
    [td]Title N [/td]
    [td]Text N… [/td]
    [td]2006-10.. [/td]
    [td]Whatever [/td]
    [td]0 [/td]
    [td]Users Name [/td]
    [td]N [/td]
  [/tr]
[/table]

Share this post


Link to post
Share on other sites
OK... I'll take a closer look; thanks for the details.

Share this post


Link to post
Share on other sites
A friend at work (An SQL Guru of sorts) suggested the following query, however he used MS SQL and MySQL Doesnt seem to like the subquery (in the brackets) and throws an error...

---------------------------------------------------------------------------------------------
SELECT
BlogPost.BlogPostID
, ImgSrc
, PostTitle
, PostText
, BlogPost.DateTime
, Category
, Deleted
, User.Name
, x.commentcount AS CommentCount

FROM BlogPost
JOIN User on BlogPost.userID=User.userID
LEFT JOIN select(BlogPostID, count(*) as commentcount from BlogPostComment) x
ON BlogPost.BlogPostID = x.BlogPostID
WHERE BlogPost.Deleted = '0'

GROUP BY BlogPost.BlogPostID
ORDER BY BlogPost.BlogPostID

-------------------------------------------------------------------------------------

Comments? Thoughts?

Share this post


Link to post
Share on other sites
Sorry I never got back to this... that query may work, but the "subquery" is really just a unnamed view a.k.a. derived table, which should be fine.  But only if the word select is inside the parentheses -- otherwise, you'll get a syntax error on all versions.

Share this post


Link to post
Share on other sites
[quote author=fenway link=topic=111020.msg455929#msg455929 date=1161548246]
Sorry I never got back to this... that query may work, but the "subquery" is really just a unnamed view a.k.a. derived table, which should be fine.  But only if the word select is inside the parentheses -- otherwise, you'll get a syntax error on all versions.
[/quote]

I tired putting select in the brackets but still getting exactly the same error message. Query was:

SELECT
BlogPost.BlogPostID
, ImgSrc
, PostTitle
, PostText
, BlogPost.DateTime
, Category
, Deleted
, User.Name
, x.commentcount AS CommentCount

FROM BlogPost

JOIN User on BlogPost.userID=User.userID
 
LEFT JOIN (select BlogPostID, count(*) as commentcount from

BlogPostComment) x
      ON BlogPost.BlogPostID = x.BlogPostID

WHERE BlogPost.Deleted = '0'

GROUP BY BlogPost.BlogPostID
ORDER BY BlogPost.BlogPostID

Share this post


Link to post
Share on other sites
I think you just need to add all the fields to the group by. try this:

[code]
SELECT BlogPost.BlogPostID, ImgSrc, PostTitle, PostText, BlogPost.DateTime, Category, Deleted, User.Name, COUNT(BlogPostComment.BlogCommentID) AS CommentCount
FROM BlogPost JOIN User USING (userID) RIGHT JOIN BlogPostComment ON BlogPost.BlogPostID = BlogPostComment.BlogPostID
WHERE BlogPost.Deleted = '0'
GROUP BY BlogPost.BlogPostID, ImgSrc, PostTitle, PostText, BlogPost.DateTime, Category, Deleted, User.Name
[/code]

Share this post


Link to post
Share on other sites
And what version is this?

Share this post


Link to post
Share on other sites
Maybe you can't use derived tables in <4.1... I'll have to check.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.