• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Query Join Code

Community Beginner ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

Can anyone tell me why this code returns an error. Whats the correct code? Thanks in advance

quote:

<cfquery datasource="#request.dsn#" name="testd">
SELECT f.forumID, f.forumName, t.forumID, t.topicID, p.topicID, p.postID
FROM Forums f LEFT OUTER JOIN (SELECT Topics.forumID, COUNT(Posts.postID) as total FROM Topics LEFT JOIN Posts ON Posts.topicID = Topics.topicID GROUP BY Topics.forumID)
Topics ON Topics.forumID = Forums.forumID
</cfquery>
TOPICS
Advanced techniques

Views

1.5K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

which table is aliased as 't'?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

Topics

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

I don't see anything aliased as p or t in your query. The sub-select is aliased as topics, not t, plus, you are not even using COUNT(Posts.postID) as total outside of the sub-select, so it is not clear to me at all what you are actually trying to accomplish with this SQL.

Plus, you should always include the error message that you are seeing, rather than letting us try and guess.

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

I want to combine the following two queries.
quote:

<cfquery datasource="#request.dsn#" name="testA">
SELECT f.forumID, f.forumName, t.forumID, t.topicID
FROM Forums f LEFT OUTER JOIN Topics t ON f.forumID = t.forumID
</cfquery>

AND
quote:

<cfquery datasource="#request.dsn#" name="testc">
SELECT Topics.forumID, COUNT(Posts.postID) as total FROM Topics LEFT JOIN Posts ON Posts.topicID = Topics.topicID GROUP BY Topics.forumID
</cfquery>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

quote:

I want to combine the following two queries.
....and do what, exactly?

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

The queries retrieve forum name and the number of posts in the forum topic. The tables are Topics, Forums, and Posts. forumID and topicID are the field names that link each of the tables.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

What does this do?

<cfquery datasource="#request.dsn#" name="testc">
SELECT f.forumName, f.forumID, t.topicID, COUNT(p.postID) as total
FROM Topics t
LEFT JOIN Posts p ON p.topicID = t.topicID
LEFT JOIN Forums f ON f.forumID = t.forumID
GROUP BY f.forumName, f.forumID, t.topicID
</cfquery>

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

The query retrieve forum name and the number of posts in the forum topic. The tables are Topics, Forums, and Posts. forumID and topicID are the field names that link each of the tables. COUNT is supposed to return the var(total) for the number of posts in that topic category.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

I meant, did it do what you were wanting your query to do?

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

well the two separate queries do yes, but I was wanting to see if I could combine them into one query.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

So, the combined query doesn't give you the desired count? Also, it wasn't clear why you are using OUTER joins.

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

The combined query says error executing database. I am using out OUTER joins cause I want it to return records even if there is no match in the other tables.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

What database are you using?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

FileMaker ... The first queries return results just fine, but the combined one fails

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

Not familiar with FileMaker. So, you are saying that this query is generating an error? Do you have detailed error information? (Debugging enabled, etc.)

SELECT f.forumName, f.forumID, t.topicID, COUNT(p.postID) as total
FROM Topics t
LEFT JOIN Posts p ON p.topicID = t.topicID
LEFT JOIN Forums f ON f.forumID = t.forumID
GROUP BY f.forumName, f.forumID, t.topicID

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

LATEST
ok my apologize... that code does work. I got it to return the right results... no more errors. Thank you so much. I really appreciate the help.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

hmm... maybe you have edited your post, 'cos i do not see the aliasing
in your code - that is what i was actually pointing out... all i see is
just:

Topics.forumID)
Topics ON Topics.forumID = Forums.forumID
</cfquery>

(note: i read the forums via nntp and do not get post edits)

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

Whenever you have a subquery in your from clause, you have to give it an alias, even if you don't use that alias anywhere else in your query.

On second thought, you have to use the alias name for your join. You are not joining to your subquery on any fields at all.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Feb 22, 2008 Feb 22, 2008

Copy link to clipboard

Copied

Ok I am confused I don't know how to do that? Can you give me an example?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation