Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need to Query and output Table 1 to a repeating region and a count from Table 2 that have Table 1 in their record

Posted on 2005-04-28
11
Medium Priority
?
228 Views
Last Modified: 2013-12-24
I'm using Coldfusion MX7 and MSSQL Server.

I have 2 tables CLUBS and USERS.

I have a page where I list all CLUBS and I want to list the number of USERS who have the particular CLUBS.ID number in their PRIMARY_CLUB field.

I already have this working with just the clubs but I now want to show a number beside the name that tell the user how many members the club has.

I've only been us CF for a month so please be specific in your responses including exactly what the variable for the count would be called.

Thanks

Rick
0
Comment
Question by:rduval
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 9

Expert Comment

by:cyberdevil67
ID: 13891665
Hi rduval,

  without knowing your table structure, you could write a query that woul inner join the two tables. Then you could then output the query like this.

  <cfoutput group="Clubs" query="queryname">
    #ClubName#
        <cfoutput>#ID# #UserName#</cfoutput>
  </cfoutput>


  Which groups the output.

  Hope that helps but without the table defs I can't write the query for you.

Cheers!
0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 13891886
SELECT column, (SELECT COUNT(*) FROM CLUBS  WHERE (PRIMARY_CLUB= CLUBS.CLUBID)) AS clubCount
FROM CLUBS

you'll have to adjust the column names if they do not match
0
 

Author Comment

by:rduval
ID: 13891989
Tacobell

I can't see how this would work as the query doesn't access the users db at all does it?
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
LVL 17

Expert Comment

by:Tacobell777
ID: 13892196
<cfquery name="queryName" datasource="yourDatasource">
SELECT column, (SELECT COUNT(*) FROM CLUBS  WHERE (PRIMARY_CLUB= CLUBS.CLUBID)) AS clubCount
FROM CLUBS
</cfquery>
0
 
LVL 14

Accepted Solution

by:
Renante Entera earned 2000 total points
ID: 13892258
Hi rduval!

I think this is what you are looking for :

<cfquery name="qGetRecord" datasource="DSN">
  SELECT c.[ID], c.[Name], count(*) as TotalMember
  FROM Clubs c
  LEFT JOIN Users u
    ON u.primary_club = c.[ID]
  GROUP BY c.[ID], c.[Name]
</cfquery>

List of Clubs with total no. of members :
<cfoutput query="qGetRecord">
#currentrow#. #Name# - #TotalMember#<br>
</cfquery>

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 

Author Comment

by:rduval
ID: 13893654
entrance2002...

It works perfectly (except for the </cfquery> on that last line that should have been </cfoutput>) but thanks.

I can't say I understand it though... I'll have to look more into LEFT JOIN!

I've awarded you the points but one question if you don't mind... I see the square brackets on some vars li c.[id] but not on u.primary_club, I've never seen square braces used in an SQL. What does c.[id] mean vs. just c.id?

Thanks again.

0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13893741
Hi rduval!

First of all thanks for accepting my answer and the given grade.

Second, I apologize for that minor mistake "</cfquery>".  I'm too careless about it, perhaps it's due to some stress and fatigue on my work.  Anyway, as long as the concept/flow is there and you've understand it.

Speaking of "c.[id]" vs. "c.id".  Actually, it's just the same.  I'm just fund of putting the column names with brackets "[]" to avoid encountering errors when it happens that the column names are reserved words.  In the future, this might be helpful to you.

Lastly, using JOINS.  For your reference, just visit this site : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_3mk3.asp

Hope this makes sense.


Regards!
eNTRANCE2002 :-)
0
 

Author Comment

by:rduval
ID: 13893758
Oh and, can I put a WHERE and an ORDER by clause in this statement somewhere, I tried but it 's crapping out with syntax errors. Is there a special place to put them in this type of statement?
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13899476
Hmmm ... :-?

rduval, I think that one deserves to be another question.  Just post another question then elaborate more about the expected result that you want to achieve.  As soon as you have posted it, you can expect me to help you.


Best regards!
eNTRANCE2002 :-)
0
 

Author Comment

by:rduval
ID: 13902422
That's ok, thanks. I've got a friend writing it for me.

Rick
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 13903510
OK.  Just let me know if you still need some assistance.


Regards!
eNTRANCE2002 :-)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question