Duh! Thanks, Alan. Worked like a charm. Now I wonder why I need to use a direct sql statement. It seems like the search action ought to be able to produce simple sql like that!
John -----Original Message----- From: Alan Wolfe [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 1:32 PM To: [EMAIL PROTECTED] Subject: Re: Witango-Talk: another sql puzzler Hi John, In a join like below, you have to have a column that links the 2 tables together, or else it will select all data from t shirts for every student or something silly like that and thats why your getting such huge counts. if tblStudents2003 has a studentid you would do it like this: select count(a1.studentid) as numorders, a1.studentid from dbo.maydayShirts a1, dbo.tblStudents2003_4 b1 where b1.yog = '2004' and a1.studentid=b1.studentid group by a1.studentid order by numorders desc Adding the a1.studentid=b1.studentid clause (or something else which links the 2 tables directly together like that) is what should do the trick for you. Hope that helps! Alan ----- Original Message ----- From: "John Newsom" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, March 25, 2004 12:48 PM Subject: Witango-Talk: another sql puzzler I have two tables, one with tshirt orders, one with students, including year of graduation (yog). I'm trying to get a count of the number of shirts ordered by each student, selecting only the current seniors. This statement, which joins the two tables, select count(a1.studentid) as numorders, a1.studentid from dbo.maydayShirts a1, dbo.tblStudents2003_4 b1 where b1.yog = '2004' group by a1.studentid order by numorders desc produces a bizzare count in the numorders column, where I get a goofy number in the numorders column. Here is a sample output: numorders studentid 565 5998 565 8429 339 8249 226 8136 226 8308 226 8342 226 7980 226 7986 226 8059 226 8074 The other statement relies on a view which joins the two tables, and works fine. select count(studentid) as numorders, studentid from dbo.tshirtsyog where yog = '2004' group by studentid order by numorders desc Here is sample output: numorders studentid 5 5998 2 5999 2 7356 2 7516 2 7561 2 7857 2 7868 2 7887 2 7980 2 7986 Is the basic lesson that if one is doing a count and a where clause, do join first? Or is there some sort of sub select statement I need? I gave up trying to use the witango search action, because I couldn't get even a simple count statement to work! Thanks! John Newsom -----Original Message----- From: Alan Wolfe [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 11:48 AM To: [EMAIL PROTECTED] Subject: Re: Witango-Talk: Something completely different Hi Gene, Could you build an array with the table names and then loop through the array doing the work for each table (using the variable in place of where you would normaly have your constant table name in the DBMS)? ----- Original Message ----- From: "Wolf, Gene" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, March 25, 2004 11:32 AM Subject: RE: Witango-Talk: Something completely different Scott, no, I could do that and that may be a solution. What I have, in essence, is a single ODBC with what amounts to multiple tables within the "database". All of the "tables" have the exact same structure so I want to loop through all of the records of one table, list them, then read all of the records of the next table, list them, etc. This is hard to explain but I hope this gives you a better idea of what I'm trying to do. Thanks for your help! -----Original Message----- From: Scott Cadillac [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 2:19 PM To: [EMAIL PROTECTED] Subject: RE: Witango-Talk: Something completely different Hi Gene, Not that I understand 100% of what you're doing, but I think you're saying each Excel file has a different ODBC connection, right? If you have an array that describes all your Excel file connections, then you can do a Loop on that and use Variables for your datasource connections - then you only need one Search Action and one set of code (for parsing the data) for the whole thing. See http://xmlx.ca/articles/571.aspx Hope this helps. Cheers.... Scott Cadillac, 403-281-6090 ~ [EMAIL PROTECTED] ------------ XML-Extranet ~ http://xmlx.ca ~ http://forums.xmlx.ca Well-formed Programming in C# .NET, Witango, MSIE and XML ------------ IExtranet ~ http://iextranet.ca Witango ~ http://witango.org EasyXSLT ~ http://easyxslt.ca IIS Watcher ~ http://iiswatcher.ca ------------ ________________________________ From: Wolf, Gene [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 11:46 AM To: [EMAIL PROTECTED] Subject: Witango-Talk: Something completely different OK, this may be way off the wall but I figured one of you folks would have tried this, or know it can't be done. I have a number of excel spreadsheets that I will be reading data from using Witango. There are about 14 of these sheets and I have working ODBC links to each one of these. Now, the format of all of these sheets is exactly the same and I need to be able to create the exact same reports from each of these spreadsheets. Is there any way I can change the table I'm looking at in either a search action or a direct DBMS so I can loop through a list of table names and create the reports without duplicating the code for every table? Does this make sense to anyone? *laughs* Gene Wolf Business Systems Analyst, TLMN DRS Optronics, Inc. 2330 Commerce Park Drive Palm Bay, Florida 32905 Phone: 321-309-0685 E-mail: [EMAIL PROTECTED] ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
