Re: Need a little sql help...
This will work, but I second the temp table idea.. select distinct name, age, addr1, addr2 from ( select f1.name, f1.age, case when f1.addr1 is null then f2.addr1 else f1.addr1 end as addr1, case when f1.addr2 is null then f2.addr2 else f1.addr2 end as addr2 from foouser f1 join foouser f2 on f1.name = f2.name ) where addr1 is not null and addr2 is not null On Mon, Jan 24, 2011 at 1:18 PM, Dean Lawrence wrote: > > Steve, > > Is the name ultimately supposed to be the unique key for this table? > Instead of battling with these multiple records, you could create a > temporary table, loop through all the records in your main table and > either insert or update a record in the temp table based upon the > name. Once you have done that, delete all the rows from your main > table and then insert the merged records from your temp table back > into the main table. You should only need to run this scrubbing > procedure once. > > Dean > > On Mon, Jan 24, 2011 at 2:13 PM, DURETTE, STEVEN J (ATTASIAIT) > wrote: >> >> That was just example data... The Name is actually a distinct number >> letter combination. When the data is passed over to me they are supposed >> to pre-merge the rows into one, but they don't so I have been tasked to >> deal with it when the data suppliers don't. Another issue is that there >> are MANY more columns, so I could actually have 20 rows with this >> situation. They all merge into one though. >> >> Sorry for the confusion, I just redact true data whenever possible. >> >> -Original Message- >> From: Greg Morphis [mailto:gmorp...@gmail.com] >> Sent: Monday, January 24, 2011 2:00 PM >> To: cf-talk >> Subject: Re: Need a little sql help... >> >> >> How do you know the 2 Steve's are the same Steve? >> What if there was another Steve who was also 40 but lived elsewhere? >> Can you provide some more realistic data? And how you'd know they were >> the same Steve? or whomever? >> >> >> > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341150 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Need a little sql help...
Steve, Is the name ultimately supposed to be the unique key for this table? Instead of battling with these multiple records, you could create a temporary table, loop through all the records in your main table and either insert or update a record in the temp table based upon the name. Once you have done that, delete all the rows from your main table and then insert the merged records from your temp table back into the main table. You should only need to run this scrubbing procedure once. Dean On Mon, Jan 24, 2011 at 2:13 PM, DURETTE, STEVEN J (ATTASIAIT) wrote: > > That was just example data... The Name is actually a distinct number > letter combination. When the data is passed over to me they are supposed > to pre-merge the rows into one, but they don't so I have been tasked to > deal with it when the data suppliers don't. Another issue is that there > are MANY more columns, so I could actually have 20 rows with this > situation. They all merge into one though. > > Sorry for the confusion, I just redact true data whenever possible. > > -Original Message- > From: Greg Morphis [mailto:gmorp...@gmail.com] > Sent: Monday, January 24, 2011 2:00 PM > To: cf-talk > Subject: Re: Need a little sql help... > > > How do you know the 2 Steve's are the same Steve? > What if there was another Steve who was also 40 but lived elsewhere? > Can you provide some more realistic data? And how you'd know they were > the same Steve? or whomever? > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341149 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Need a little sql help...
That was just example data... The Name is actually a distinct number letter combination. When the data is passed over to me they are supposed to pre-merge the rows into one, but they don't so I have been tasked to deal with it when the data suppliers don't. Another issue is that there are MANY more columns, so I could actually have 20 rows with this situation. They all merge into one though. Sorry for the confusion, I just redact true data whenever possible. -Original Message- From: Greg Morphis [mailto:gmorp...@gmail.com] Sent: Monday, January 24, 2011 2:00 PM To: cf-talk Subject: Re: Need a little sql help... How do you know the 2 Steve's are the same Steve? What if there was another Steve who was also 40 but lived elsewhere? Can you provide some more realistic data? And how you'd know they were the same Steve? or whomever? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341148 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Need a little sql help...
How do you know the 2 Steve's are the same Steve? What if there was another Steve who was also 40 but lived elsewhere? Can you provide some more realistic data? And how you'd know they were the same Steve? or whomever? On Mon, Jan 24, 2011 at 12:57 PM, DURETTE, STEVEN J (ATTASIAIT) wrote: > > Hi all, > > > > My SQL mojo seems to have left me. Here is the situation, I have 1 > table. In this table there are some times two rows that should have > actually been one. Here is an example to explain. > > > > Table: Name, Age, address one, address two > > > > Row 1: Steve,40,123 Anystreet,NULL > > Row 2: Steve,40,NULL,456 Anystreet > > > > What should have been passed to me would have been: > > Steve,40,123 Anystreet,456 Anystreet > > > > The actual tables have a lot more columns and there are a few that this > can happen with. The columns are either (null and not null values) or > match exactly across rows. > > > > Any ideas what the SQL would look like to return a single row for each > with all of the fields merged? > > > > Thanks, > > Steve > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341147 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Need a little sql help...
Hi all, My SQL mojo seems to have left me. Here is the situation, I have 1 table. In this table there are some times two rows that should have actually been one. Here is an example to explain. Table: Name, Age, address one, address two Row 1: Steve,40,123 Anystreet,NULL Row 2: Steve,40,NULL,456 Anystreet What should have been passed to me would have been: Steve,40,123 Anystreet,456 Anystreet The actual tables have a lot more columns and there are a few that this can happen with. The columns are either (null and not null values) or match exactly across rows. Any ideas what the SQL would look like to return a single row for each with all of the fields merged? Thanks, Steve ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341146 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) sql help
Figured it out. I was missing a join. Should have been: SELECT DISTINCT phases.phase AS phaseLabel, narratives.title, narratives.id, narratives.content, phases.id AS phaseId FROM( ( nsftool.phases2company phases2company INNER JOIN nsftool.phases phases ON (phases2company.phaseId = phases.id)) INNER JOIN nsftool.narratives narratives ON (narratives.phase = phases.id)) INNER JOIN nsftool.narratives2case narratives2case ON (narratives2case.narrativeId = narratives.id) AND (phases2company.caseId = narratives2case.caseId) WHERE (narratives2case.caseId = ) ORDER BY phases2company.displayRank ASC, narratives.displayRank ASC On Fri, Oct 15, 2010 at 1:35 PM, Michael J. Sprague wrote: > > I am having a SQL problem that I'm guessing amounts to me just being brain > dead today but I'd really appreciate any help with this. Here is the query: > > SELECT phases.phase AS phaseLabel, > narratives.title, > narratives.id, > narratives.content, > phases.id AS phaseId > FROM(( nsftool.narratives2case narratives2case > INNER JOIN > nsftool.narratives narratives > ON (narratives2case.narrativeId = narratives.id)) > INNER JOIN > nsftool.phases2case phases2case > ON (phases2case.caseId = narratives2case.caseId)) > INNER JOIN > nsftool.phases phases > ON (phases2case.phaseId = phases.id) > WHERE (narratives2case.caseId = null="no" value="#trim(caseId)#" />) > ORDER BY phases2case.displayRank ASC, narratives.displayRank ASC > > It is almost working but it is currently returning the same narrative > titles > and content for each phaseId. What I need to do is return the phases for a > particular case ordered by their phase display rank and return the > associated narrative content ordered by the narrative display rank. > Currently it returns the same set of narrative content for each phase. Any > ideas? > > Thanks in advance for any help with this. > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338239 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
(ot) sql help
I am having a SQL problem that I'm guessing amounts to me just being brain dead today but I'd really appreciate any help with this. Here is the query: SELECT phases.phase AS phaseLabel, narratives.title, narratives.id, narratives.content, phases.id AS phaseId FROM(( nsftool.narratives2case narratives2case INNER JOIN nsftool.narratives narratives ON (narratives2case.narrativeId = narratives.id)) INNER JOIN nsftool.phases2case phases2case ON (phases2case.caseId = narratives2case.caseId)) INNER JOIN nsftool.phases phases ON (phases2case.phaseId = phases.id) WHERE (narratives2case.caseId = ) ORDER BY phases2case.displayRank ASC, narratives.displayRank ASC It is almost working but it is currently returning the same narrative titles and content for each phaseId. What I need to do is return the phases for a particular case ordered by their phase display rank and return the associated narrative content ordered by the narrative display rank. Currently it returns the same set of narrative content for each phase. Any ideas? Thanks in advance for any help with this. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338238 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) Transact-SQL Help
Brad, I'll have to look at your response in much greater detail, but I can tell you this. Currently, I'm running a CF script that populates a prospect_export table once a day. The initial query returns 25,785 rows, which gets flattened into 20,265 rows for reporting purposes. Ultimately I'd rather this were a view, but barring that, I'd rather repopulate the table more often than once a day, and I'm fairly certain that doing it with SQL instead of CF would be more efficient. -- Rick Root CFFM - Open Source Coldfusion File Manager http://www.opensourcecf.com/cffm ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326228 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: (ot) Transact-SQL Help
> As long as you are only dealing with a dozen or so records > from the database it should perform fine and and I think it will be > a heck of a lot simpler than trying to make your SQL server take a > row-based list of people and pivot them out into columns. Assuming the example is actually representative of the data, partitioning with row_number() may be a feasible alternative to pivoting or cursors. But it would probably still require a bit of fancy footwork in terms of sql. Having said that, Brad raises some valid questions about the data. Is it really that small and simple a recordset, and if so what is the perceived advantage of doing it in sql over using CF ? -Leigh ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326216 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: (ot) Transact-SQL Help
> Original Message > Subject: (ot) Transact-SQL Help > From: Rick Root > Date: Thu, September 10, 2009 12:25 pm > To: cf-talk > > > I'm hoping someone here can point me in the right direction. I'm > doing something in CF that I really need to be doing in SQL: > After reading your post several times I think I might understand just what it is you are trying to accomplish here. Questions: What makes you think you need to do this in SQL? Is the current code performing poorly? Are there really hundreds of columns and this is just a simplified example? Are there really only 13 records in the ptTeam table or are you actually doing dozens of times on one page? Is there any particular difference between EXEC1 and EXEC2 or is it just up to the randomness of the query as to who gets to be 1 and who gets to be 2? Do you know if you will have at least one of every type of person? Since you asked-- as for SQL solutions, it looks like you are basically wanting a column for each record matching a particular criteria. My first thought is a messy select statement that joins to the ptTeam table once for every column. This will actually be made harder if the order of the members of the same type is not explicit. Note: you would need to change each join that isn't guaranteed to return a match to an LEFT OUTER JOIN. I'm also making the wild assumption that your records all share the same entityid but have a unique MEMBERID. You also didn't say what column you wanted to return for each member so I assumed you wanted the MEMBERID column. SELECT TOP 1 EXEC1.MEMBERID AS EXEC1, EXEC1.MEMBERID AS EXEC2, TEAMLEAD.MEMBERID AS TEAMLEAD, etc... FROM ptTeam EXEC1 INNER JOIN ptTeam EXEC2 ON EXEC1.entityid = 'foo' AND EXEC1.status = 1 AND reltype='ETL' AND EXEC1.MEMBERID <> EXEC2.MEMBERID INNER JOIN ptTeam TEAMLEAD ON EXEC1.entityid = 'foo' AND TEAMLEAD.status = 1 AND TEAMLEAD.leaderFlag=1 INNER JOIN etc... WHERE EXEC1.status = 1 AND reltype='ETL' AND EXEC1.entityid = 'foo' For the record, I would expect this to perform like crap since it's going to make every possible combination and then pick one to return with TOP 1. Moving on, another option would be to declare a table variable to hold your final result set: DECLARE @finalResult TABLE (entityid int, EXEC1 int, EXEC2 int, TEAMLEAD int, MEMBER1 int, MEMBER2 int, MEMBER3 int, MEMBER4 int etc...) Then populate your columns one statement at a time: INSERT INTO @finalResult (entityid, EXEC1) (SELECT entityid, MEMEBRID FRKM PTRACK.dbo.ptTeam WHERE status=1 AND reltype='ETL' UPDATE fr SET fr.EXEC2 = team.MEMBERID FROM @finalResult fr INNER JOIN ptTeam team ON fr.entityid = team.entityid AND team.status=1 AND team.reltype='ETL' AND fr.EXEC1 <> team.MEMBERID UPDATE fr SET fr.TEAMLEAD = lead.MEMBERID FROM @finalResult fr INNER JOIN ptTeam lead ON fr.entityid = lead.entityid AND lead.status=1 AND lead.leaderFlag=1 etc... Ok, now that I gave those examples let me say I think they're both pretty crappy. Honestly I'd do this in CF if it were me based on what you showed us, but I would do it with query of queries. Return your 15 or so records in a single, simple hit to the database. Then perform 6 qofqs on it-- one for each type of member. In other words, get all the EXECs in one, and all team lead in another, and then just loop over them as you go and output them as EXEC1, EXEC2, TEAMLEAD, etc. As long as you are only dealing with a dozen or so records from the database it should perform fine and and I think it will be a heck of a lot simpler than trying to make your SQL server take a row-based list of people and pivot them out into columns. That never works easily because it really isn't the kind of thing SQL Server was designed to do. On the cursor thing-- that is another option I didn't present, but I really don't think it's a better one. Thanks. ~Brad ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326214 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) Transact-SQL Help
You might also take a look at ms sql 2005's row_number() function. You may be able to partition the data and use row_number() to limit the returned records. http://msdn.microsoft.com/en-us/library/ms186734.aspx ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326213 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: (ot) Transact-SQL Help
> This is where the limiting nature of SQL comes in--SQL is a set language > and has no (or very limited) looping/reshaping capability. Unless MS > has extended their SQL in ways I'm not aware of, this would be nearly > impossible. That's where the power of CF comes in. That's not true. You can use cursors to iterate through a recordset, for example. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more informatio ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326210 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: (ot) Transact-SQL Help
Hi Rick, This is where the limiting nature of SQL comes in--SQL is a set language and has no (or very limited) looping/reshaping capability. Unless MS has extended their SQL in ways I'm not aware of, this would be nearly impossible. That's where the power of CF comes in. --Ben Rick Root wrote: > I'm hoping someone here can point me in the right direction. I'm > doing something in CF that I really need to be doing in SQL: > > I need to flatten this data: > > select > A.entityid, > A.MEMBERID, > A.RELTYPE, > A.leaderFlag > from > PTRACK.dbo.ptTeam A > WHERE > A.status=1 > > into this format: > > entityid, > EXEC1, (1st row reltype='ETL') > EXEC2, (2nd row reltype='ETL') > TEAMLEAD, (1st row leaderFlag=1) > MEMBER1,(1st row reltype='TMM' and leaderflag=0) > MEMBER2,(2nd row reltype='TMM' and leaderflag=0) > MEMBER3,(3rd row reltype='TMM' and leaderflag=0) > MEMBER4,(4th row reltype='TMM' and leaderflag=0) > MEMBER5,(5th row reltype='TMM' and leaderflag=0) > AFSTAFF1, (1st row reltype='AFS') > AFSTAFF2, (2nd row reltype='AFS') > AFVOL,, (1st row reltype='AFV') > DOCTOR1,, (1st row reltype='AMD') > DOCTOR2,, (1st row reltype='AMD') > > > The query returns multiple rows per entity. The output will be one > row per entity. > > In CF, I'm looping over the first query, and creating a resultset that > looks like the second query. If an entity has more than 1 executive > team leader or more than 5 team members or more than 2 doctors > associated, we ignore the extras. > > But I don't have the slightest friggin' clue how to do this in SQL. > > My db is MS SQL Server 2005 > > Thanks > > -- Ben Connerb...@webworldinc.com Web World, Inc. 888-206-6486 PO Box 1122 480-704-2000 Queen Creek, AZ 85242 ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326208 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
(ot) Transact-SQL Help
I'm hoping someone here can point me in the right direction. I'm doing something in CF that I really need to be doing in SQL: I need to flatten this data: select A.entityid, A.MEMBERID, A.RELTYPE, A.leaderFlag from PTRACK.dbo.ptTeam A WHERE A.status=1 into this format: entityid, EXEC1, (1st row reltype='ETL') EXEC2, (2nd row reltype='ETL') TEAMLEAD, (1st row leaderFlag=1) MEMBER1,(1st row reltype='TMM' and leaderflag=0) MEMBER2,(2nd row reltype='TMM' and leaderflag=0) MEMBER3,(3rd row reltype='TMM' and leaderflag=0) MEMBER4,(4th row reltype='TMM' and leaderflag=0) MEMBER5,(5th row reltype='TMM' and leaderflag=0) AFSTAFF1, (1st row reltype='AFS') AFSTAFF2, (2nd row reltype='AFS') AFVOL,, (1st row reltype='AFV') DOCTOR1,, (1st row reltype='AMD') DOCTOR2,, (1st row reltype='AMD') The query returns multiple rows per entity. The output will be one row per entity. In CF, I'm looping over the first query, and creating a resultset that looks like the second query. If an entity has more than 1 executive team leader or more than 5 team members or more than 2 doctors associated, we ignore the extras. But I don't have the slightest friggin' clue how to do this in SQL. My db is MS SQL Server 2005 Thanks -- Rick Root CFFM - Open Source Coldfusion File Manager http://www.opensourcecf.com/cffm ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326206 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Yep, EXISTS will virtually always be faster, usually MUCH faster, than a correlated subquery, because a subquery is evaluated for EVERY ROW processed by the outer query. On Wed, Aug 26, 2009 at 12:43 AM, Mark Henderson wrote: > > Brian Kotek wrote: > > > > WHERE NOT EXISTS should also work. > > > > Yes it does, and I knew about that method when using NOT IN, as it was > a simple change to my original working query. What I didn't know, but > now do after some googling, is that NOT EXISTS means it uses an index > in the subquery as opposed to a full table scan (which was my primary > concern). Thanks Brian. > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325745 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Brian Kotek wrote: > > WHERE NOT EXISTS should also work. > Yes it does, and I knew about that method when using NOT IN, as it was a simple change to my original working query. What I didn't know, but now do after some googling, is that NOT EXISTS means it uses an index in the subquery as opposed to a full table scan (which was my primary concern). Thanks Brian. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325707 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
WHERE NOT EXISTS should also work. On Tue, Aug 25, 2009 at 5:27 AM, Mark Henderson wrote: > > Greetings from the chilly south, > > I have this query and it returns the expected result set, but I can't > work out how to use a join instead of the NOT IN clause and I *know* > that is going to be more efficient. Basically, I want to exclude the > current special from the result set. Any ideas? > > > SELECT >TOP 1 > RV.ID AS RecID >,RV.Date_Viewed >,FS.ID >,FS.Category_ID >,FS.Title >,FS.Comment >,FS.Thumbnail >,FS.Photo >,FS.Price >,FS.Year >,FS.Date_Modified >,FS.Active >,C.Name As Category_Name >,C.Image_Path >,C.Image_Dir > FROM >tbl_ForSaleCategories C > INNER JOIN ( >tbl_RecentlyViewed RV >INNER JOIN >tbl_ForSale FS >ON RV.ID = FS.ID > ) > ON C.ID = FS.Category_ID > WHERE RV.ID NOT IN ( >SELECT ID >FROM tbl_CoverSpecial > ) > AND FS.Active = 1 > ORDER BY Date_Viewed ASC; > > > All help appreciated. TIA > > > adieu > Mark > ---*/ > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325706 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Thanks Billy and Michael (and Kevin). After some trial and error I managed to stumble upon solution (the outer join gave me results but not the expected set). SELECT TOP 1 RV.ID AS Rec_ID ,RV.Date_Viewed ,FS.ID ,FS.Category_ID ,FS.Title ,FS.Comment ,FS.Thumbnail ,FS.Photo ,FS.Price ,FS.Year ,FS.Date_Modified ,C.Name As Category_Name ,C.Image_Path ,C.Image_Dir FROM tbl_RecentlyViewed RV INNER JOIN ( tbl_ForSaleCategories C INNER JOIN ( tbl_ForSale FS LEFT JOIN tbl_CoverSpecial CS ON FS.ID = CS.ID ) ON C.ID = FS.Category_ID ) ON RV.ID = FS.ID WHERE FS.Active = 1 AND CS.ID IS NULL It can probably be improved but it works so I'm satisfied :-) adieu Mark ---*/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325705 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
Try this: ... FROM tbl_ForSaleCategories C INNER JOIN ( tbl_RecentlyViewed RV INNER JOIN tbl_ForSale FS ON RV.ID = FS.ID ) ON C.ID = FS.Category_ID LEFT JOIN tbl_CoverSpecial CS ON RV.ID = CS.ID WHERE CS.ID IS NULL AND FS.Active = 1 ORDER BY Date_Viewed ASC; Mark Henderson wrote: > Greetings from the chilly south, > > I have this query and it returns the expected result set, but I can't > work out how to use a join instead of the NOT IN clause and I *know* > that is going to be more efficient. Basically, I want to exclude the > current special from the result set. Any ideas? > > > SELECT > TOP 1 > RV.ID AS RecID > ,RV.Date_Viewed > ,FS.ID > ,FS.Category_ID > ,FS.Title > ,FS.Comment > ,FS.Thumbnail > ,FS.Photo > ,FS.Price > ,FS.Year > ,FS.Date_Modified > ,FS.Active > ,C.Name As Category_Name > ,C.Image_Path > ,C.Image_Dir > FROM > tbl_ForSaleCategories C > INNER JOIN ( > tbl_RecentlyViewed RV > INNER JOIN > tbl_ForSale FS > ON RV.ID = FS.ID > ) > ON C.ID = FS.Category_ID > WHERE RV.ID NOT IN ( > SELECT ID > FROM tbl_CoverSpecial > ) > AND FS.Active = 1 > ORDER BY Date_Viewed ASC; > > > All help appreciated. TIA > > > adieu > Mark > ---*/ > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325678 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Help
Try using an OUTER JOIN and specify the criteria in the JOIN statement ... FROM table1 LEFT OUTER JOIN table2 ON table1.col1 = table2.col1 AND {criteria goes here} ... Thanks, Mike -Original Message- From: Mark Henderson [mailto:shadefro...@gmail.com] Sent: Tuesday, August 25, 2009 4:27 AM To: cf-talk Subject: SQL Help Greetings from the chilly south, I have this query and it returns the expected result set, but I can't work out how to use a join instead of the NOT IN clause and I *know* that is going to be more efficient. Basically, I want to exclude the current special from the result set. Any ideas? SELECT TOP 1 RV.ID AS RecID ,RV.Date_Viewed ,FS.ID ,FS.Category_ID ,FS.Title ,FS.Comment ,FS.Thumbnail ,FS.Photo ,FS.Price ,FS.Year ,FS.Date_Modified ,FS.Active ,C.Name As Category_Name ,C.Image_Path ,C.Image_Dir FROM tbl_ForSaleCategories C INNER JOIN ( tbl_RecentlyViewed RV INNER JOIN tbl_ForSale FS ON RV.ID = FS.ID ) ON C.ID = FS.Category_ID WHERE RV.ID NOT IN ( SELECT ID FROM tbl_CoverSpecial ) AND FS.Active = 1 ORDER BY Date_Viewed ASC; All help appreciated. TIA adieu Mark ---*/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325673 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
Mark, You might be right but I never got that to work myself, in MSSQL. An experiment you might try is to do that subquery separately in a different CFQUERY then plug in the retrieved value. Occasionally I have found that knid of trick is quicker. Kevin On Tue, Aug 25, 2009 at 11:49 AM, Mark Henderson wrote: > > Kevin Roche wrote: > > > > Mark, > > > > What DBMS are you using? > > > > If its SQL Server, I don't think what you want to do is possible other > than > > how you have already done it. If you find another way to do it (with a > join) > > I would also be interested to see that. > > > > Kevin Roche > > > > Hi Kevin, > > Yes it's MS SQL. Are you sure I can't do a left join and use IS NULL > for the record that isn't allowed to match? > > If someone can tell me that the subquery is only run once then I'm not > too perturbed and will leave it as is. > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325662 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Kevin Roche wrote: > > Mark, > > What DBMS are you using? > > If its SQL Server, I don't think what you want to do is possible other than > how you have already done it. If you find another way to do it (with a join) > I would also be interested to see that. > > Kevin Roche > Hi Kevin, Yes it's MS SQL. Are you sure I can't do a left join and use IS NULL for the record that isn't allowed to match? If someone can tell me that the subquery is only run once then I'm not too perturbed and will leave it as is. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325660 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Mark, What DBMS are you using? If its SQL Server, I don't think what you want to do is possible other than how you have already done it. If you find another way to do it (with a join) I would also be interested to see that. Kevin Roche ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325659 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL Help
Greetings from the chilly south, I have this query and it returns the expected result set, but I can't work out how to use a join instead of the NOT IN clause and I *know* that is going to be more efficient. Basically, I want to exclude the current special from the result set. Any ideas? SELECT TOP 1 RV.ID AS RecID ,RV.Date_Viewed ,FS.ID ,FS.Category_ID ,FS.Title ,FS.Comment ,FS.Thumbnail ,FS.Photo ,FS.Price ,FS.Year ,FS.Date_Modified ,FS.Active ,C.Name As Category_Name ,C.Image_Path ,C.Image_Dir FROM tbl_ForSaleCategories C INNER JOIN ( tbl_RecentlyViewed RV INNER JOIN tbl_ForSale FS ON RV.ID = FS.ID ) ON C.ID = FS.Category_ID WHERE RV.ID NOT IN ( SELECT ID FROM tbl_CoverSpecial ) AND FS.Active = 1 ORDER BY Date_Viewed ASC; All help appreciated. TIA adieu Mark ---*/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:325658 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
> C'mon, if they are users and they have access to the system, if they go > crazy, > do they really need SQL injection to harm the system ? > They can simply delete all what they have access to, they can replace > content by porn, whatever. > Will CFQURYPARAM protect your application against that ? Presumably, they have limited access to what they're allowed to change, and nothing else. User security is all about two things - authenticating users and limiting their access to specific items. You're acting as if the second concern doesn't exist. It does, within any sufficiently large environment. This is why enterprises don't let their users log in as local admins, why they buy content filters for outbound messaging, etc, etc, etc. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324083 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
>>Also, you talk about this like your speaking of only internal applications or applications that could never go on a production environment. Indeed, we were talking about building SQL queries in a variable inside a Content management system. Of course, for parts of the site exposed to public, things are different. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324081 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Help
> If you don't take security in mind when writing > applications it's just a matter of time before > something bad happens. I can't tell you how many times I've been contacted by people who have had their site broken and need an emergency fix. I've made quite a bit of money fixing other people's broken code. One way or another, your client is going to pay for this basic level of security. Either they pay you to do it right when the code is written, or they pay a specialist like me to come in and repair the damage later (plus the cost of lost business, lost reputation, etc. resulting from the hack). The former is far less expensive than the latter. -Justin Scott ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324080 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Help
Maybe that's what your customers are like, but trust me there are many, many hackers out their just waiting to find a website to do xss attacks or sql injection attacks. Your customers may not be computer savvy, but try explaining to them that their database got deleted because you weren't taking the precautions to make sure their database was being protected. Also, you talk about this like your speaking of only internal applications or applications that could never go on a production environment. You sell these custom tags. http://customtags.internetique.com/ And from the looks of it your feeding in sql into the custom tags just as you mentioned earlier. "http://customtags.internetique.com/mapData/canadaMaps.cfm?m=4"; "http://customtags.internetique.com/mapData/canadaMaps.cfm?m=4;--"; - Broken Page I would recommend looking into this further because if you're not careful you're going to get a lawsuit. The company I work for's website has approximately 80 attempted/failed hack attempts a day. Both XSS and SQl injection, mostly sql injection. If you don't take security in mind when writing applications it's just a matter of time before something bad happens. -Original Message- From: Claude Schneegans [mailto:schneeg...@internetique.com] Sent: Tuesday, June 30, 2009 11:19 AM To: cf-talk Subject: Re: SQL Help >>With XSS they can do that while making it look like someone else did it. Probably, but my clients barely know the difference between a computer and a toaster, and I spend more of my time explaining them that in order to "press Ctrl", they must find a key on their keyboard with the letters "Ctrl" on it, and not press "C" then "t", then "r" then "l"... than fighting against malicious nerds ;-) Ah, oh yes, also when you see "Press Ctrl" and something, you must press the Ctrl key and the other key IN THE SAME TIME, otherwise it won't work. The best for that is to use TWO fingers, possibly from two different hands... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324079 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
>>With XSS they can do that while making it look like someone else did it. Probably, but my clients barely know the difference between a computer and a toaster, and I spend more of my time explaining them that in order to "press Ctrl", they must find a key on their keyboard with the letters "Ctrl" on it, and not press "C" then "t", then "r" then "l"... than fighting against malicious nerds ;-) Ah, oh yes, also when you see "Press Ctrl" and something, you must press the Ctrl key and the other key IN THE SAME TIME, otherwise it won't work. The best for that is to use TWO fingers, possibly from two different hands... ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324076 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
With SQL injection they can delete what they don't have access to. With XSS they can do that while making it look like someone else did it. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/6/30 Claude Schneegans : > > >>Internal security problems are far more common than external ones. > Within a large organization, not all users may be trustworthy. > > C'mon, if they are users and they have access to the system, if they go > crazy, > do they really need SQL injection to harm the system ? > They can simply delete all what they have access to, they can replace > content by porn, whatever. > Will CFQURYPARAM protect your application against that ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324075 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
>>Internal security problems are far more common than external ones. Within a large organization, not all users may be trustworthy. C'mon, if they are users and they have access to the system, if they go crazy, do they really need SQL injection to harm the system ? They can simply delete all what they have access to, they can replace content by porn, whatever. Will CFQURYPARAM protect your application against that ? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324074 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Sure, as long as the CMS has no XSS attack points... mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/6/30 Claude Schneegans : > > >>And with preserveSingleQuotes() you have to hope you're better at > cleaning input than hackers are at writing SQL injection. > > When I'm talking about a CMS, I'm talking about some tool some customers > have paid for > and that is only accessible by approved users with authentication. > Now if they want to hack and sabotage their own application they have > paid for, > it's their problem, and if it ever happens, they will pay again to get > their own mess cleaned up ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324073 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
> When I'm talking about a CMS, I'm talking about some tool some customers > have paid for and that is only accessible by approved users with > authentication. > Now if they want to hack and sabotage their own application they have > paid for, it's their problem, and if it ever happens, they will pay again to > get > their own mess cleaned up. Internal security problems are far more common than external ones. Within a large organization, not all users may be trustworthy. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324072 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Help
Yeah, I agree, and I think the fact that you couldn't sanitize your inputs with cfqueryparam is a big reason to not do that. If it were me, and I had either multiple queries that I wanted to be executed at the same time or I had a custom tag or component that I needed to feed in sql too, I would just use a stored proc and feed in the name. -Original Message- From: Scott Brady [mailto:dsbr...@gmail.com] Sent: Tuesday, June 30, 2009 9:52 AM To: cf-talk Subject: Re: SQL Help There's no reason you need a variable to do multiple updates in a single query statement. You can still put the SQL inside the query tags and, as you say, separate the statements with a semi-colon. Scott On Tue, Jun 30, 2009 at 6:55 AM, Stephane Vantroyen wrote: > > I don't agree with that : sometimes you have to do multiple updates, inserts or else at the same time, depending on your process and some conditions; instead of doing multiple (and thus multiple db connections), it is sometimes cool to be able to put all the statement(s) in a variable. You then can do one cfquery for the all list of statements at once (juste separate the statements with a ";", in the string variable that you create). -- - Scott Brady http://www.scottbrady.net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324071 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
There's no reason you need a variable to do multiple updates in a single query statement. You can still put the SQL inside the query tags and, as you say, separate the statements with a semi-colon. Scott On Tue, Jun 30, 2009 at 6:55 AM, Stephane Vantroyen wrote: > > I don't agree with that : sometimes you have to do multiple updates, inserts > or else at the same time, depending on your process and some conditions; > instead of doing multiple (and thus multiple db connections), it is > sometimes cool to be able to put all the statement(s) in a variable. You then > can do one cfquery for the all list of statements at once (juste separate the > statements with a ";", in the string variable that you create). -- - Scott Brady http://www.scottbrady.net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324069 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
>>And with preserveSingleQuotes() you have to hope you're better at cleaning input than hackers are at writing SQL injection. When I'm talking about a CMS, I'm talking about some tool some customers have paid for and that is only accessible by approved users with authentication. Now if they want to hack and sabotage their own application they have paid for, it's their problem, and if it ever happens, they will pay again to get their own mess cleaned up. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324067 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Help
Hmm.. yeah I can see your point. There are instances where it would be ok to feed in an entire sql statement through cf variables. I think my point was just that this shouldn't be a regular practice as it's unnecessary. It also makes editing the sql statement more difficult and it's more bulky and more difficult to read. Also, as Claude stated in his email there are instances where you could have a custom tag or a component that is really scalable and you may need to feed in sql statements through to it. I can see the benefit of that. -Original Message- From: Stephane Vantroyen [mailto:s...@emakina.com] Sent: Tuesday, June 30, 2009 8:55 AM To: cf-talk Subject: Re: SQL Help "it's not good practice in ColdFusion to do this. " I don't agree with that : sometimes you have to do multiple updates, inserts or else at the same time, depending on your process and some conditions; instead of doing multiple (and thus multiple db connections), it is sometimes cool to be able to put all the statement(s) in a variable. You then can do one cfquery for the all list of statements at once (juste separate the statements with a ";", in the string variable that you create). If you encounter the problem of the quotes (number of extra single quotes in the statement generated upon your variable), just use the PreserveSingleQuotes() function; basically, your query will look like : ('mystring' is the variable that contains your statements) #PreserveSingleQuotes(mystring)# >As Dominic said, putting the entire sql statement in as a variable in >ColdFusion isn't necessary. Many languages like php that sort of thing is >necessary but in ColdFusion, as Dominic said, it's just redundant. > >However, just for future knowledge sake, the reason your receiving this >error is because when you feed in a variable with single quotes, for some >reason they become duplicated when you feed it in sql. I've actually never >seen this before mostly because it's not good practice in ColdFusion to do >this. > >However, Just FYI though... the below query should work. > > > >#replace(sqlToRun,"''","'","ALL")# > > >Paul Alkema > > >Basically, the is kind of redundant and >negates the benefit of the cfquery tag. Put all you SQL inside the >cfquery tag. cfqueryparam is only valid within cfquery tags. > >Dominic >> >> CF 8.01 OS X. >> >> I have: >> >> >1, 'jason', '2009-06-27') >> >> Which runs fine in a SQL Editor, but running that in the above I >get: >> >> You have an error in your SQL syntax; check the manual that corresponds to >your MySQL server version for the right syntax to use near '1'', ''1'', >''jason'', ''2009-06-27'')' at line 1 >> >> There are a number of extra single quotes that I dont see why. >> >> If I try converting to I have something messed up because I >get an error: >> >> I did: >> cfsqltype="CF_SQL_INTEGER">, cfsqltype="CF_SQL_INTEGER">, cfsqltype="CF_SQL_VARCHAR">, cfsqltype="CF_SQL_DATE">)"> ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324066 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
And with preserveSingleQuotes() you have to hope you're better at cleaning input than hackers are at writing SQL injection. And yes, we all know you're totally awesome at it; this response is for others who'd rather not make that bet. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/6/30 Claude Schneegans : > > >>As Dominic said, putting the entire sql statement in as a variable in > ColdFusion isn't necessary. > > Please, there IS a very good reason one would put an SQL statement in a > variable: > when using some tool to generate build queries for instance. > I have many examples in my own CMS, like a report builder, search > engine, etc. > > >>However, just for future knowledge sake, the reason your receiving this > error is because when you feed in a variable with single quotes, for some > reason they become duplicated when you feed it in sql. I've actually never > seen this before mostly because it's not good practice in ColdFusion to do > this. > > The "some" reason is simply that any single quote in values in the > statement must be escaped, > and CF will do it automatically for any variable inside a CFQUERY tag. > If you supply your own SQL statement, you are responsible for escaping > single quotes yourself, > and you must prevent CF to do it, because CF is not able to distinguish > quotes in the statement > from quotes in values, this is the purpose of the pseudo function > preventSingleQuotes(). > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324065 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
>>As Dominic said, putting the entire sql statement in as a variable in ColdFusion isn't necessary. Please, there IS a very good reason one would put an SQL statement in a variable: when using some tool to generate build queries for instance. I have many examples in my own CMS, like a report builder, search engine, etc. >>However, just for future knowledge sake, the reason your receiving this error is because when you feed in a variable with single quotes, for some reason they become duplicated when you feed it in sql. I've actually never seen this before mostly because it's not good practice in ColdFusion to do this. The "some" reason is simply that any single quote in values in the statement must be escaped, and CF will do it automatically for any variable inside a CFQUERY tag. If you supply your own SQL statement, you are responsible for escaping single quotes yourself, and you must prevent CF to do it, because CF is not able to distinguish quotes in the statement from quotes in values, this is the purpose of the pseudo function preventSingleQuotes(). ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324064 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
"it's not good practice in ColdFusion to do this. " I don't agree with that : sometimes you have to do multiple updates, inserts or else at the same time, depending on your process and some conditions; instead of doing multiple (and thus multiple db connections), it is sometimes cool to be able to put all the statement(s) in a variable. You then can do one cfquery for the all list of statements at once (juste separate the statements with a ";", in the string variable that you create). If you encounter the problem of the quotes (number of extra single quotes in the statement generated upon your variable), just use the PreserveSingleQuotes() function; basically, your query will look like : ('mystring' is the variable that contains your statements) #PreserveSingleQuotes(mystring)# >As Dominic said, putting the entire sql statement in as a variable in >ColdFusion isn't necessary. Many languages like php that sort of thing is >necessary but in ColdFusion, as Dominic said, it's just redundant. > >However, just for future knowledge sake, the reason your receiving this >error is because when you feed in a variable with single quotes, for some >reason they become duplicated when you feed it in sql. I've actually never >seen this before mostly because it's not good practice in ColdFusion to do >this. > >However, Just FYI though... the below query should work. > > > >#replace(sqlToRun,"''","'","ALL")# > > >Paul Alkema > > >Basically, the is kind of redundant and >negates the benefit of the cfquery tag. Put all you SQL inside the >cfquery tag. cfqueryparam is only valid within cfquery tags. > >Dominic >> >> CF 8.01 OS X. >> >> I have: >> >> >1, 'jason', '2009-06-27') >> >> Which runs fine in a SQL Editor, but running that in the above I >get: >> >> You have an error in your SQL syntax; check the manual that corresponds to >your MySQL server version for the right syntax to use near '1'', ''1'', >''jason'', ''2009-06-27'')' at line 1 >> >> There are a number of extra single quotes that I dont see why. >> >> If I try converting to I have something messed up because I >get an error: >> >> I did: >> cfsqltype="CF_SQL_INTEGER">, cfsqltype="CF_SQL_INTEGER">, cfsqltype="CF_SQL_VARCHAR">, cfsqltype="CF_SQL_DATE">)"> ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324063 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Help
As Dominic said, putting the entire sql statement in as a variable in ColdFusion isn't necessary. Many languages like php that sort of thing is necessary but in ColdFusion, as Dominic said, it's just redundant. However, just for future knowledge sake, the reason your receiving this error is because when you feed in a variable with single quotes, for some reason they become duplicated when you feed it in sql. I've actually never seen this before mostly because it's not good practice in ColdFusion to do this. However, Just FYI though... the below query should work. #replace(sqlToRun,"''","'","ALL")# Paul Alkema -Original Message- From: Dominic Watson [mailto:watson.domi...@googlemail.com] Sent: Tuesday, June 30, 2009 5:22 AM To: cf-talk Subject: Re: SQL Help Basically, the is kind of redundant and negates the benefit of the cfquery tag. Put all you SQL inside the cfquery tag. cfqueryparam is only valid within cfquery tags. Dominic 2009/6/28 Jason Slack : > > CF 8.01 OS X. > > I have: > > > > #sqlToRun# > > > #sqlToRun# > > > the is: > INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(1, 1, 'jason', '2009-06-27') > > Which runs fine in a SQL Editor, but running that in the above I get: > > You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1'', ''1'', ''jason'', ''2009-06-27'')' at line 1 > > There are a number of extra single quotes that I dont see why. > > If I try converting to I have something messed up because I get an error: > > I did: > , , , )"> > > and I get an error: Invalid CFML construct found on line 22 at column 120. > > Does anyone see my issue? > > -Jason > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324062 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Basically, the is kind of redundant and negates the benefit of the cfquery tag. Put all you SQL inside the cfquery tag. cfqueryparam is only valid within cfquery tags. Dominic 2009/6/28 Jason Slack : > > CF 8.01 OS X. > > I have: > > > > #sqlToRun# > > > #sqlToRun# > > > the is: > INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(1, 1, > 'jason', '2009-06-27') > > Which runs fine in a SQL Editor, but running that in the above I > get: > > You have an error in your SQL syntax; check the manual that corresponds to > your MySQL server version for the right syntax to use near '1'', ''1'', > ''jason'', ''2009-06-27'')' at line 1 > > There are a number of extra single quotes that I dont see why. > > If I try converting to I have something messed up because I > get an error: > > I did: > cfsqltype="CF_SQL_INTEGER">, cfsqltype="CF_SQL_INTEGER">, cfsqltype="CF_SQL_VARCHAR">, cfsqltype="CF_SQL_DATE">)"> > > and I get an error: Invalid CFML construct found on line 22 at column 120. > > Does anyone see my issue? > > -Jason > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324056 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
A) Always use . (Note the period.) B) When in doubt, use anyways. (Note the period.) C) While preserveSingleQuotes() can be a useful tool at times, I would have a very difficult time thinking of a time where I would use it. D) Always use . (Note again, the period.) E) You cannot use in the midst of a . F) Always use . (Once again, a period.) INSERT INTO personalevent ( eventid, userid, username, eventdate ) VALUES ( , , , ); G) In case you missed it, ALWAYS use . PERIOD. :-) HTH ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324030 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
Right I am switching everything to as I read about SQL injection. Do you see my Invalid CFML construct found on line 22 at column 120. above though? I still dont. -Jason >if you're going to be generating your SQL like that, you'll need to wrap >your final variable in preserveSingleQuotes(). > >so... > > > #preserveSingleQuotes(sqlToRun)# > > >it will be pointed out to you (possibly before I even finish composing this >response), that you are leaving yourself open to SQL injection attacks by >not using . Hopefully you're at least taking other steps to >sanitize the inputs :) > >On Sun, Jun 28, 2009 at 11:23 AM, Jas > >> ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324029 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
if you're going to be generating your SQL like that, you'll need to wrap your final variable in preserveSingleQuotes(). so... #preserveSingleQuotes(sqlToRun)# it will be pointed out to you (possibly before I even finish composing this response), that you are leaving yourself open to SQL injection attacks by not using . Hopefully you're at least taking other steps to sanitize the inputs :) On Sun, Jun 28, 2009 at 11:23 AM, Jason Slack wrote: > > CF 8.01 OS X. > > I have: > > > > #sqlToRun# > > > #sqlToRun# > > > the is: > INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(1, > 1, 'jason', '2009-06-27') > > Which runs fine in a SQL Editor, but running that in the above I > get: > > You have an error in your SQL syntax; check the manual that corresponds to > your MySQL server version for the right syntax to use near '1'', ''1'', > ''jason'', ''2009-06-27'')' at line 1 > > There are a number of extra single quotes that I dont see why. > > If I try converting to I have something messed up because I > get an error: > > I did: > cfsqltype="CF_SQL_INTEGER">, cfsqltype="CF_SQL_INTEGER">, cfsqltype="CF_SQL_VARCHAR">, cfsqltype="CF_SQL_DATE">)"> > > and I get an error: Invalid CFML construct found on line 22 at column 120. > > Does anyone see my issue? > > -Jason > > ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324027 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL Help
CF 8.01 OS X. I have: #sqlToRun# #sqlToRun# the is: INSERT INTO personalevent(eventid, userid, username, eventdate) VALUES(1, 1, 'jason', '2009-06-27') Which runs fine in a SQL Editor, but running that in the above I get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1'', ''1'', ''jason'', ''2009-06-27'')' at line 1 There are a number of extra single quotes that I dont see why. If I try converting to I have something messed up because I get an error: I did: , , , )"> and I get an error: Invalid CFML construct found on line 22 at column 120. Does anyone see my issue? -Jason ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324026 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL help..
I'm really greatful to you for the post. >Just answered this on the SQL list: >http://www.houseoffusion.com/groups/sql/thread.cfm/threadid:855 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314544 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL help..
Just answered this on the SQL list: http://www.houseoffusion.com/groups/sql/thread.cfm/threadid:855 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314541 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL help..
Hello there, I do appreciate this isn't the right forum for posting this question but I have posted my question in the SQL forum but I haven't heard anything back. It's a very straightforward query... at least I hope it is for someone with good sql skills. I was hoping someone could help here. I have a table called 'phone_numbers' with two columns, one called 'person_name' and the other called 'number'. A person can have more than one number and I want the query to return all person's that have more than 3 numbers. Jamie 1 Jamie 2 Jamie 3 Jamie 4 Allan 5 Vicki 6 Marilyn 7 Paul8 Scott 9 Scott 10 Scott 11 Scott 12 So in the above example, the query would return Jamie and Scott. How do I get this data? Regards, John ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314540 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
> It also begs the question, if they were only supposed to have one > entry > per e-mail address, why wasn't there error checking or a constraint on > > the table to force this in the first place? True enough. I was thinking the same thing myself ;-) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312620 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
> The records are from contest entries. People can only enter with one email > address. Some people entered multiple times, using the same email address. So, why all the complexity with joins and subqueries? Just... SELECT DISTINCT email FROM sometable then pick a winner from the list of unique e-mail addresses. Go back and grab one of the entries with that e-mail address at random for the contact information. This way you're picking from a list of unique e-mail addresses that entered the drawing. It also begs the question, if they were only supposed to have one entry per e-mail address, why wasn't there error checking or a constraint on the table to force this in the first place? -Justin Scott, http://www.tlson.com/ ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312618 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
The records are from contest entries. People can only enter with one email address. Some people entered multiple times, using the same email address. I need to get a record set used to pick a winner, including just one of the records from the duplicate email entries. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312616 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
> I need to get a record set used to pick a winner, including just one > of the records from the duplicate email entries. If it does not matter which one, try the query I posted in my first response. The syntax is not tested, but it has the right concept. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312614 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
The records are from contest entries. People can only enter with one email address. Some people entered multiple times, using the same email address. I need to get a record set used to pick a winner, including just one of the records from the duplicate email entries. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312607 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
> You could try something like this. Not tested, but the idea is to > select a single PK for each email. Then use a JOIN to display the > details for those PK's. Note, the previous query assumes it does not matter which record is returned. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312599 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
> When there are records with duplicate > emails addresses, I need to include one of them. So you are trying to display one record for each email address? You could try something like this. Not tested, but the idea is to select a single PK for each email. Then use a JOIN to display the details for those PK's. SELECT m.lastname, m.firstname, m.city, m.state FROM mytable AS m INNER JOIN ( SELECT email, max(YourPK) AS YourPK FROMmyTable GROUP BY email ) as e ON m.YourPK = e.YourPK ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312598 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Are the other fields in your table the same when the email is the same? Meaning, are the records really duplicate? Or is it just the email that is duplicate and the other fields may have varying values for two rows that have the same email? If they do vary, do you care which of the "duplicate" rows you return? Judah On Tue, Sep 16, 2008 at 7:50 AM, Jeff F <[EMAIL PROTECTED]> wrote: > Thanks Jim. Now I see. > > I guess what I'm looking for would be something like this then: > > SELECT mytable.lastname,mytable.email > FROM mytable GROUP BY mytable.email HAVING distinct(mytable.email) > > Which of course does not work. When there are records with duplicate emails > addresses, I need to include one of them. > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312597 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
Thanks Jim. Now I see. I guess what I'm looking for would be something like this then: SELECT mytable.lastname,mytable.email FROM mytable GROUP BY mytable.email HAVING distinct(mytable.email) Which of course does not work. When there are records with duplicate emails addresses, I need to include one of them. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312588 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
On Tue, Sep 16, 2008 at 10:13 AM, Jeff F <[EMAIL PROTECTED]> wrote: > SELECT distinct mytable.email > FROM mytable > I get 19588 as a recordcount. This number would include email addresses that are duplicated in the table (but only a count of 1 for each distinct address). >SELECT mytable.lastname, > mytable.email >FROM mytable >GROUP BY mytable.email HAVING count(mytable.email) = 1 > I get 19162 as a recordcount. ?? This recordcount eliminates any email addresses that are duplicated in the table. Note that any email addresses that are duplicated are NOT INCLUDED in the results using this method. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312587 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
Jim, At first glance that seems to work, however the recordcounts appear to be off. What I did was a simple query to find the total number of distinct email addresses: SELECT distinct mytable.email FROM mytable I get 19588 as a recordcount. When I run SELECT mytable.lastname, mytable.email FROM mytable GROUP BY mytable.email HAVING count(mytable.email) = 1 I get 19162 as a recordcount. ?? I don't understand where the difference is? -John ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312585 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Help
Sorry about the output part in the bottom; that was from my test query. Should be: select distinct email from mytable select lastname, firstname, city, state from mytable where email = '#select_distinct_email.email#' #lastname# #firstname# #city# #state# > -Original Message- > From: Rick Faircloth [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 16, 2008 9:42 AM > To: CF-Talk > Subject: RE: SQL Help > > To view all your records with duplicate email addresses, > you might try something like this: > > > > select distinct email > from mytable > > > > > > > > select lastname, firstname, city, state > from mytable > where email = '#select_distinct_email.email#' > > > > > > #city_state# #office_name# > > > > > > > Check that output and see if that's what you're looking for... > > Rick > > > > > -Original Message- > > From: Jeff F [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, September 16, 2008 8:58 AM > > To: CF-Talk > > Subject: SQL Help > > > > I've got a table (MySQL) with about 20k records. I'd like to be able to get > > all fields from the > table > > with distinct email addresses. Essentially, I'm weeding out records with > > duplicate email > addresses. > > > > What I'm trying does not work: > > > > SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state > > FROM mytable > > WHERE mytable.email in (select distinct mytable.email) > > > > What am I missing?? > > > > -Johny B > > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312577 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
On Tue, Sep 16, 2008 at 8:58 AM, Jeff F <[EMAIL PROTECTED]> wrote: > I've got a table (MySQL) with about 20k records. I'd like to be able to get > all fields from the table with distinct email addresses. Essentially, I'm > weeding out records with duplicate email addresses. > > What I'm trying does not work: > > SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state > FROM mytable > WHERE mytable.email in (select distinct mytable.email) > What you are doing there will return every record in the table. If you really want to eliminate any records where there are duplicate emails, you could do this: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state, mytable.email FROM mytable GROUP BY mytable.email HAVING count(mytable.email) = 1 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312579 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Help
To view all your records with duplicate email addresses, you might try something like this: select distinct email from mytable select lastname, firstname, city, state from mytable where email = '#select_distinct_email.email#' #city_state# #office_name# Check that output and see if that's what you're looking for... Rick > -Original Message- > From: Jeff F [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 16, 2008 8:58 AM > To: CF-Talk > Subject: SQL Help > > I've got a table (MySQL) with about 20k records. I'd like to be able to get > all fields from the table > with distinct email addresses. Essentially, I'm weeding out records with > duplicate email addresses. > > What I'm trying does not work: > > SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state > FROM mytable > WHERE mytable.email in (select distinct mytable.email) > > What am I missing?? > > -Johny B > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312576 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help
Assuming your PK is named customerID you can do the following: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state FROM mytable WHERE mytable.customerID in (select min(customerID) from mytable group by email) There is at least one problem in your query. The in () statement is missing a From statement so that it should read: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state FROM mytable WHERE mytable.email in (select distinct mytable.email from mytable) But this will not work since all records (even the doublettes) match the criteria. Gert Jeff F schrieb: > I've got a table (MySQL) with about 20k records. I'd like to be able to get > all fields from the table with distinct email addresses. Essentially, I'm > weeding out records with duplicate email addresses. > > What I'm trying does not work: > > SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state > FROM mytable > WHERE mytable.email in (select distinct mytable.email) > > What am I missing?? > > -Johny B > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312575 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL Help
I've got a table (MySQL) with about 20k records. I'd like to be able to get all fields from the table with distinct email addresses. Essentially, I'm weeding out records with duplicate email addresses. What I'm trying does not work: SELECT mytable.lastname, mytable.firstname, mytable.city, mytable.state FROM mytable WHERE mytable.email in (select distinct mytable.email) What am I missing?? -Johny B ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:312573 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql help <> NEQ
ok i go it i think but it just looks so weird to me.. it looks back wards but works... SELECT DISTINCT TOP 100 PERCENT dbo.V_riprod_ZMATMAST.sap_partnum AS NEQnumb FROM dbo.V_riprod_ZMATMAST LEFT OUTER JOIN dbo.V_riprod_Specs_ZMATMAST_EQ ON dbo.V_riprod_ZMATMAST.sap_partnum = dbo.V_riprod_Specs_ZMATMAST_EQ.sap_partnum WHERE (dbo.V_riprod_Specs_ZMATMAST_EQ.sap_partnum IS NULL) ORDER BY dbo.V_riprod_ZMATMAST.sap_partnum ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302563 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
sql help <> NEQ
ok i have 2 views V_riprod_ZMATMAST returns 60 records V_riprod_Specs_ZMATMAST_EQ returns 39 records or so that are matched in another table. these i will do an update on problem i am having is the NEQ <> i am trying to use the view below to return all the results of the records that don't match. so i can do an Insert... SELECT DISTINCT TOP 100 PERCENT ABS(dbo.V_riprod_ZMATMAST.sap_partnum) AS pnum2 FROM dbo.V_riprod_ZMATMAST INNER JOIN dbo.V_riprod_Specs_ZMATMAST_EQ ON dbo.V_riprod_ZMATMAST.sap_partnum <> dbo.V_riprod_Specs_ZMATMAST_EQ.sap_partnum WHERE (dbo.V_riprod_ZMATMAST.sap_partnum <> N'dbo.V_riprod_Specs_ZMATMAST_EQ.sap_partnum') i am using mssql2000 ent viewer. have tried left outer join & a bunch of wierd stuff, and just keep getting funky results... This works for counting rcdCT TotalCount EqCount NeqCount 1 60 39 21 SELECT TOP 100 PERCENT COUNT(*) AS rcdCT, dbo.V_riprod_Specs_ZMATMAST_Count.rcdCT AS TotalCount, dbo.V_riprod_Specs_ZMATMAST_EQ_Count.rcdCT AS EqCount, dbo.V_riprod_Specs_ZMATMAST_Count.rcdCT - dbo.V_riprod_Specs_ZMATMAST_EQ_Count.rcdCT AS NeqCount FROM dbo.V_riprod_Specs_ZMATMAST_Count CROSS JOIN dbo.V_riprod_Specs_ZMATMAST_EQ_Count GROUP BY dbo.V_riprod_Specs_ZMATMAST_Count.rcdCT, dbo.V_riprod_Specs_ZMATMAST_EQ_Count.rcdCT ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302515 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: sql help!!!!!!!!!!
no dice cause it was 2am and didnt want to think about it no more > > Dunno if this is right, just woke up, but I'll take a stab at it. > Looks like you need another join to the upsell table, then order by > its sort by column first. > > SELECT brands.brand_id, brands.brand_name, brands.brand_logo, > products.product_id, products.brand_id, products.product_item, > products.category_id, products.product_image, products. > product_archived, products.product_discontinued, upsell.upsell_id, > upsell.product_id, upsell.upsell_product_id, upsell.upsell_sort > FROM products, brands, upsell > WHERE products.brand_id = brands.brand_id > AND products.product_id = upsell.product_id > AND products.product_id in (20,21) > ORDER BY upsell.upsell_sort ASC, > brands.brand_name ASC, > products.product_item ASC > > And what's with the... sql help!! > > Sounds like you're goin' down on the titanic! :) > > Will > > > > > > > ~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293726 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: sql help!!!!!!!!!!
> any good suggestions? Dunno if this is right, just woke up, but I'll take a stab at it. Looks like you need another join to the upsell table, then order by its sort by column first. SELECT brands.brand_id, brands.brand_name, brands.brand_logo, products.product_id, products.brand_id, products.product_item, products.category_id, products.product_image, products. product_archived, products.product_discontinued, upsell.upsell_id, upsell.product_id, upsell.upsell_product_id, upsell.upsell_sort FROM products, brands, upsell WHERE products.brand_id = brands.brand_id AND products.product_id = upsell.product_id AND products.product_id in (20,21) ORDER BY upsell.upsell_sort ASC, brands.brand_name ASC, products.product_item ASC And what's with the... sql help!! Sounds like you're goin' down on the titanic! :) Will ~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293711 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
sql help!!!!!!!!!!
ok i need to put Upsell items underneath the "add upsell items" form, so that they can see what upsell products they currently have for that product. (mysql 5 btw) So it is all good but then they asked for me to make it "sort-able" and that is causing me a headache and can't quite get that last bit in there correctly. 3 tables and the fields I need from them upsell upsell_id, product_id, upsell_product_id, upsell_sort products product_id, brand_id, product_item brands brand_id, brand_name product id is taken from url upsell_product_id is just a second product_id field(so product_id might be 19 and upsell_product_id might be 25, which is just actually product 25) So i get and filter the actual product by url.id then I need to grab any matching records in the upsell table and get the upsell_product_id and then match that to a product in the product table and then match that to a brand in the brand table. --- On the page I am showing this Brand| Product| Sort | Delete I can use this to get close to what I need but it doesn't have the sort from upsells and the (20,21) would be a list that I need to get the product info on (would need to be from a previous query. select brands.brand_id, brands.brand_name, brands.brand_logo, products.product_id, products.brand_id, products.product_item, products.category_id, products.product_image, products.product_archived, products.product_discontinued fromproducts, brands where products.brand_id = brands.brand_id and products.product_id in (20,21) order bybrands.brand_name asc, products.product_item asc any good suggestions? ~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293705 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
OT: SQL Help
I'm having a weird query issue with SQL server... The following query inserts two rows into the query_results table: insert into ENQUIRE.dbo.QUERY_RESULTS ( QUERYID, ENTITY_ID ) SELECT DISTINCT 1933 AS QUERYID, A.ENTITY_ID FROM BIOTAB1 A inner join WEBREPORTS.dbo.TB912 TB912 on A.ENTITY_ID=TB912.ENTITYID -- inner join WEBREPORTS.dbo.TB967 TB967 on A.ENTITY_ID=TB967.ENTITYID WHERE 1=1 AND A.PRIMARY_RECORD_STATUS IN ('A','-') AND EXISTS ( SELECT 1 FROM AFFIL X WHERE X.ENTITY_ID = A.ENTITY_ID AND X.RECORD_TYPE IN ('TS') ) AND ( 1=0 OR ( 1=1 and TB912.MAILCODE IN ('YAN') -- ) OR ( -- 1=1 and TB967.EVENTCDE IN ('AF55') ) ) That's correct behavior. If I run the select statement by itself, it returns 2 records. But if I uncomment the three lines that are commented out, the query only inserts ONE record. The SELECT statement run by itself (with the commented out lines uncommented) returns two records. Why won't it insert the two records? I'm just running this stuff in SQL Server Management Studio, but the results from Coldfusion are the same. Rick ~| Get involved in the latest ColdFusion discussions, product development sharing, and articles on the Adobe Labs wiki. http://labs/adobe.com/wiki/index.php/ColdFusion_8 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291974 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help, please ...
[EMAIL PROTECTED] wrote: > HostName, iDate, Package > client1, 2007-06-01, Update1 > client1, 2007-06-01, Update2 > client1, 2007-06-01, Update3 > client1, 2007-06-02, Update5 > client1, 2007-06-02, NewApp > client2, 2007-06-01, Update1 > client2, 2007-06-01, Update2 > client2, 2007-06-01, Update3 > client2, 2007-06-02, Update5 > client2, 2007-06-02, NewApp > > What I want is a report/summary like result that looks like this > > HostName, iDate, Installs > client1, 2007-06-01, 3 > client1, 2007-06-02, 2 > client2, 2007-06-01, 3 > client2, 2007-06-02, 2 SELECT HostName, iDate, COUNT(Package) FROM installs GROUP BY HostName, iDate ORDER BY HostName, iDate Jochem ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282372 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Help, please ...
Let me get this straight, you want a report to summarize the number of installs by date and client. So client 1 ran 3 installs on 6/1 and 2 installs on 6/2. I believe you simply need to group by hostname, and then date and then use an aggregate function (count()) to add up the records in between like so: SELECT hostname, iDate, count(1) FROM installs GROUP BY hostname, iDate ORDER BY hostname, iDate The order by is optional, but I threw it in since your result set was ordered that way. Depending on whether or not your iDate column stores time or not, you may need to convert it to date only. (syntax depends on your DB). ~Brad -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 26, 2007 10:16 PM To: CF-Talk Subject: SQL Help, please ... Hello, It's been a wile since I have had to do much in the way of SQL queries and I need some help. I have a table called "installs" that looks like this HostName, iDate, Package client1, 2007-06-01, Update1 client1, 2007-06-01, Update2 client1, 2007-06-01, Update3 client1, 2007-06-02, Update5 client1, 2007-06-02, NewApp client2, 2007-06-01, Update1 client2, 2007-06-01, Update2 client2, 2007-06-01, Update3 client2, 2007-06-02, Update5 client2, 2007-06-02, NewApp What I want is a report/summary like result that looks like this HostName, iDate, Installs client1, 2007-06-01, 3 client1, 2007-06-02, 2 client2, 2007-06-01, 3 client2, 2007-06-02, 2 I hope this makes sense and I know this is off topic but any hep would be great. Thanks, tom ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282367 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
SQL Help, please ...
Hello, It's been a wile since I have had to do much in the way of SQL queries and I need some help. I have a table called "installs" that looks like this HostName, iDate, Package client1, 2007-06-01, Update1 client1, 2007-06-01, Update2 client1, 2007-06-01, Update3 client1, 2007-06-02, Update5 client1, 2007-06-02, NewApp client2, 2007-06-01, Update1 client2, 2007-06-01, Update2 client2, 2007-06-01, Update3 client2, 2007-06-02, Update5 client2, 2007-06-02, NewApp What I want is a report/summary like result that looks like this HostName, iDate, Installs client1, 2007-06-01, 3 client1, 2007-06-02, 2 client2, 2007-06-01, 3 client2, 2007-06-02, 2 I hope this makes sense and I know this is off topic but any hep would be great. Thanks, tom ~| CF 8 â Scorpio beta now available, easily build great internet experiences â Try it now on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282317 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
ANYONE? Pretty please?? more SQL help....
Heck, I did up a special page just to explain my needs better. :) http://wtomlinson.com/eval.html been flailing away at this for quite a while now... Thanks, Will ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:27 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Need more SQL help....
Ok, I have one last query to get right in my eval app. Here's my schema: http://wtomlinson.com/evalschema.gif Jim has been kind enough to help work out my queries. Now, I just need to rollup my rankings questions and I'm done (mostly). The data looks like this: tblquestions: Rank these tools in order of learning curve tblanswersets CF ..NET PHP tblanswers 1 2 3 Answer get inserted into an output table, tblevalanswerresults, where they can be counted. Here's what I need in plain english: Show me a list of questions. With each question, show me a list of the answers according to what they were ranked, listed highes ranked to lowest. Here's a query I'm workin with, the data isn't right tho. It just counts up the answers for each answerset. SELECT Q.questionID, Q.questionsetID, Q.question, Q.questioncatid, QAX.questionid, QAX.answersetid, ASets.answersetid, ASets.answersettypeid, ASets.answersetname, AST.answersettypename, ASAX.answersetid, ASAX.answerid, A.answerID, A.answername, A.answervalue, (SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations e2 ON e1.evalid = e2.evalid WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.answerID = A.answerID AND e1.questionid = Q.questionID) AS totalforthisquestion, (SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations e2 ON e1.evalid = e2.evalid WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.questionid = Q.questionID) AS totalforinstructor, ((SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations e2 ON e1.evalid = e2.evalid WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.answerID = A.answerID AND e1.questionid = Q.questionID)/ (SELECT count(*) FROM tblevalanswerresults e1 INNER JOIN tblevaluations e2 ON e1.evalid = e2.evalid WHERE e2.InstructorID = '#SESSION.CollegeID#' AND e1.questionid = Q.questionID)*100) AS answerpercentage FROM tblquestions Q INNER JOIN tblquestionsanswers_x QAX ON Q.questionID = QAX.questionID INNER JOIN tblanswersets ASets ON QAX.answersetid = ASets.answersetid INNER JOIN tblanswersettypes AST ON AST.answersettypeid = ASets.answersettypeid INNER JOIN tblanswersetsanswers_x ASAX ON QAX.answersetid = ASAX.answersetid INNER JOIN tblanswers A ON ASAX.answerid = A.answerid WHERE Q.questioncatid = 3 /* Additional Q = 3*/ AND ASets.answersettypeid = 3 /* Ranking answers only = 3*/ Thanks much, Will ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275469 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help - Answered
removing the DISTINCT did the trick. Thank you both. Josh Nathanson wrote: > I don't think you need DISTINCT at all if you are using GROUP BY. GROUP BY > will group all the matching rate values together automatically, so the > result will be all distinct values. Try something like (not tested) > > SELECT Count(rate) as ratecount, rate > FROM myrates > etc. > GROUP BY rate > > Dump that and see if you're any closer to what you want. > > -- Josh > > > - Original Message - > From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > To: "CF-Talk" > Sent: Tuesday, April 03, 2007 2:09 PM > Subject: Re: SQL Help - Answered > > > >> I'm going to have to look at something. I'm still not getting what I >> anticipated. >> >> There are over 3700 records of which 1775 of them are distinct values >> for "RATE". However, I am only getting values of 1 for COUNT(DISTINCT >> rate) as rateCount. I was trying to find out how many records are there >> for each distinct value in the RATE field. I need to chart how many >> records there are for each value in RATE. >> >> Thanks for the help. >> >> >> >> Greg Morphis wrote: >> >>> No problem, if you want to know "why" take a look at aggregate >>> functions, which is what "count" is, as well as others.. >>> >>> On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >>> >>> >>>> Thank you >>>> >>>> Greg Morphis wrote: >>>> >>>> >>>>> You need a group by in your query.. >>>>> >>>>> >>>>> >>>>>> SELECT count(DISTINCT rate) as rateCount, rate >>>>>> FROMmyrates >>>>>> WHERE my_code = 385 and year = 2005 >>>>>> >>>>>> >>>>>> >>>>> GROUP BY rate >>>>> >>>>> >>>>> >>>>>> ORDER BY rate >>>>>> >>>>>> >>>>>> >>>>> On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >>>>> >>>>> >>>>> >>>>>> Why do the first two queries work and the last one fail? The only >>>>>> difference is adding the comma and additional field to the SELECT >>>>>> statement. >>>>>> >>>>>> >>>>>> SELECT count(DISTINCT rate) as rateCount >>>>>> FROMmyrates >>>>>> WHERE my_code = 385 and year = 2005 >>>>>> ORDER BY rate >>>>>> >>>>>> >>>>>> >>>>>> SELECT rate >>>>>> FROMmyrates >>>>>> WHERE my_code = 385 and year = 2005 >>>>>> ORDER BY rate >>>>>> >>>>>> >>>>>> >>>>>> FAIL >>>>>> >>>>>> >>>>>> SELECT count(DISTINCT rate) as rateCount, rate >>>>>> FROMmyrates >>>>>> WHERE my_code = 385 and year = 2005 >>>>>> ORDER BY rate >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274507 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help - Answered
I don't think you need DISTINCT at all if you are using GROUP BY. GROUP BY will group all the matching rate values together automatically, so the result will be all distinct values. Try something like (not tested) SELECT Count(rate) as ratecount, rate FROM myrates etc. GROUP BY rate Dump that and see if you're any closer to what you want. -- Josh - Original Message - From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Tuesday, April 03, 2007 2:09 PM Subject: Re: SQL Help - Answered > I'm going to have to look at something. I'm still not getting what I > anticipated. > > There are over 3700 records of which 1775 of them are distinct values > for "RATE". However, I am only getting values of 1 for COUNT(DISTINCT > rate) as rateCount. I was trying to find out how many records are there > for each distinct value in the RATE field. I need to chart how many > records there are for each value in RATE. > > Thanks for the help. > > > > Greg Morphis wrote: >> No problem, if you want to know "why" take a look at aggregate >> functions, which is what "count" is, as well as others.. >> >> On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> >>> Thank you >>> >>> Greg Morphis wrote: >>> >>>> You need a group by in your query.. >>>> >>>> >>>>> SELECT count(DISTINCT rate) as rateCount, rate >>>>> FROMmyrates >>>>> WHERE my_code = 385 and year = 2005 >>>>> >>>>> >>>> GROUP BY rate >>>> >>>> >>>>> ORDER BY rate >>>>> >>>>> >>>> >>>> >>>> On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >>>> >>>> >>>>> Why do the first two queries work and the last one fail? The only >>>>> difference is adding the comma and additional field to the SELECT >>>>> statement. >>>>> >>>>> >>>>> SELECT count(DISTINCT rate) as rateCount >>>>> FROMmyrates >>>>> WHERE my_code = 385 and year = 2005 >>>>> ORDER BY rate >>>>> >>>>> >>>>> >>>>> SELECT rate >>>>> FROMmyrates >>>>> WHERE my_code = 385 and year = 2005 >>>>> ORDER BY rate >>>>> >>>>> >>>>> >>>>> FAIL >>>>> >>>>> >>>>> SELECT count(DISTINCT rate) as rateCount, rate >>>>> FROMmyrates >>>>> WHERE my_code = 385 and year = 2005 >>>>> ORDER BY rate >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>> >> >> > > ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274483 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help - Answered
can you provide a sample of your data and the way your table is designed (column name, type)? Thanks On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I'm going to have to look at something. I'm still not getting what I > anticipated. > > There are over 3700 records of which 1775 of them are distinct values > for "RATE". However, I am only getting values of 1 for COUNT(DISTINCT > rate) as rateCount. I was trying to find out how many records are there > for each distinct value in the RATE field. I need to chart how many > records there are for each value in RATE. > > Thanks for the help. > > > > Greg Morphis wrote: > > No problem, if you want to know "why" take a look at aggregate > > functions, which is what "count" is, as well as others.. > > > > On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > >> Thank you > >> > >> Greg Morphis wrote: > >> > >>> You need a group by in your query.. > >>> > >>> > SELECT count(DISTINCT rate) as rateCount, rate > FROMmyrates > WHERE my_code = 385 and year = 2005 > > > >>> GROUP BY rate > >>> > >>> > ORDER BY rate > > > >>> > >>> > >>> On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > >>> > >>> > Why do the first two queries work and the last one fail? The only > difference is adding the comma and additional field to the SELECT > statement. > > > SELECT count(DISTINCT rate) as rateCount > FROMmyrates > WHERE my_code = 385 and year = 2005 > ORDER BY rate > > > > SELECT rate > FROMmyrates > WHERE my_code = 385 and year = 2005 > ORDER BY rate > > > > FAIL > > > SELECT count(DISTINCT rate) as rateCount, rate > FROMmyrates > WHERE my_code = 385 and year = 2005 > ORDER BY rate > > > > > > > > >>> > >> > > > > > > ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274482 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help - Answered
I'm going to have to look at something. I'm still not getting what I anticipated. There are over 3700 records of which 1775 of them are distinct values for "RATE". However, I am only getting values of 1 for COUNT(DISTINCT rate) as rateCount. I was trying to find out how many records are there for each distinct value in the RATE field. I need to chart how many records there are for each value in RATE. Thanks for the help. Greg Morphis wrote: > No problem, if you want to know "why" take a look at aggregate > functions, which is what "count" is, as well as others.. > > On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > >> Thank you >> >> Greg Morphis wrote: >> >>> You need a group by in your query.. >>> >>> SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 >>> GROUP BY rate >>> >>> ORDER BY rate >>> >>> >>> On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >>> >>> Why do the first two queries work and the last one fail? The only difference is adding the comma and additional field to the SELECT statement. SELECT count(DISTINCT rate) as rateCount FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate SELECT rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate FAIL SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate >>> >> > > ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274481 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help - Answered
No problem, if you want to know "why" take a look at aggregate functions, which is what "count" is, as well as others.. On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Thank you > > Greg Morphis wrote: > > You need a group by in your query.. > > > >> SELECT count(DISTINCT rate) as rateCount, rate > >> FROMmyrates > >> WHERE my_code = 385 and year = 2005 > >> > > GROUP BY rate > > > >> ORDER BY rate > >> > > > > > > > > > > On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > >> Why do the first two queries work and the last one fail? The only > >> difference is adding the comma and additional field to the SELECT > >> statement. > >> > >> > >> SELECT count(DISTINCT rate) as rateCount > >> FROMmyrates > >> WHERE my_code = 385 and year = 2005 > >> ORDER BY rate > >> > >> > >> > >> SELECT rate > >> FROMmyrates > >> WHERE my_code = 385 and year = 2005 > >> ORDER BY rate > >> > >> > >> > >> FAIL > >> > >> > >> SELECT count(DISTINCT rate) as rateCount, rate > >> FROMmyrates > >> WHERE my_code = 385 and year = 2005 > >> ORDER BY rate > >> > >> > >> > >> > >> > >> > > > > > > ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274480 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Help - Answered
Thank you Greg Morphis wrote: > You need a group by in your query.. > >> SELECT count(DISTINCT rate) as rateCount, rate >> FROMmyrates >> WHERE my_code = 385 and year = 2005 >> > GROUP BY rate > >> ORDER BY rate >> > > > > > On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > >> Why do the first two queries work and the last one fail? The only difference >> is adding the comma and additional field to the SELECT statement. >> >> >> SELECT count(DISTINCT rate) as rateCount >> FROMmyrates >> WHERE my_code = 385 and year = 2005 >> ORDER BY rate >> >> >> >> SELECT rate >> FROMmyrates >> WHERE my_code = 385 and year = 2005 >> ORDER BY rate >> >> >> >> FAIL >> >> >> SELECT count(DISTINCT rate) as rateCount, rate >> FROMmyrates >> WHERE my_code = 385 and year = 2005 >> ORDER BY rate >> >> >> >> >> >> > > ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274479 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Help
You need a group by in your query.. > SELECT count(DISTINCT rate) as rateCount, rate > FROMmyrates > WHERE my_code = 385 and year = 2005 GROUP BY rate > ORDER BY rate On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Why do the first two queries work and the last one fail? The only difference > is adding the comma and additional field to the SELECT statement. > > > SELECT count(DISTINCT rate) as rateCount > FROMmyrates > WHERE my_code = 385 and year = 2005 > ORDER BY rate > > > > SELECT rate > FROMmyrates > WHERE my_code = 385 and year = 2005 > ORDER BY rate > > > > FAIL > > > SELECT count(DISTINCT rate) as rateCount, rate > FROMmyrates > WHERE my_code = 385 and year = 2005 > ORDER BY rate > > > > > ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274477 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL Help
Why do the first two queries work and the last one fail? The only difference is adding the comma and additional field to the SELECT statement. SELECT count(DISTINCT rate) as rateCount FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate SELECT rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate FAIL SELECT count(DISTINCT rate) as rateCount, rate FROMmyrates WHERE my_code = 385 and year = 2005 ORDER BY rate ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274476 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need More SQL help - DRIVING ME NUTS!
Ok Jim, this is getting the results I'm after. The counts are correct, everything looks right so far. I just needta test it some more. Would you please email me offlist? WT at WTOMLINSON.com Thanks a ton dude! Will ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273289 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need More SQL help - DRIVING ME NUTS!
Thanks a ton Jim! I'll give it a shot later today and let you know the results. Thanks! Will ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273267 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need More SQL help - DRIVING ME NUTS!
Three things I noted... 1. There are a lot of duplicated columns in the result list...I removed those to make it easier to read and debug 2. It looks to me like tblevalanswerresults needs to be joined to both tblquestions and tblanswers...see below 3. When you join on tblEvaluations like you did previously, and then filter with a WHERE clause, you may get instances where you won't return some of the questions in a set because none were answered for that instructor. I moved the filtering on instructor up into the join. If this doesn't work, just do a dump of each table and post that somewhere. It will be easier to debug with the whole picture. SELECT Q.questionID,Q.questionsetID,Q.question,ARS.answersetname,A.answerID,A.answername,A.answervalue, COUNT(EAR.answerID) AS numAnswers FROM tblquestions Q INNER JOIN tblanswersets ARS USING (answerSetID) INNER JOIN tblanswers A USING (answerSetID) LEFT JOIN (tblevalanswerresults EAR INNER JOIN tblEvaluations E ON E.evalID = EAR.evalID AND E.InstructorID = '#SESSION.CollegeID#') ON A.answerID = EAR.answerID AND Q.QuestionID = EAR.QuestionID GROUP BY Q.questionID,Q.questionsetID,Q.question,ARS.answersetname,A.answerID,A.answername,A.answervalue ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273259 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need More SQL help - DRIVING ME NUTS!
>No been following this thread, but can you do the first two parts? Yes, I've got this part working with Jochem's fine SQL. > >What about the third part? > >"With each possible answer, show me a count of the ones that were selected" Nope > >Do those then try to combine them. > >Adrian This is where the problem is. I haven't figured out how to combine them, group them, whatever needs to be done, I haven't gotten it right yet. I can't quit on this until it works. Here's the data retrieval as it stands now: http://wtomlinson.com/evalQRYs/qryDatabig.gif Here's the data in my many table: http://wtomlinson.com/evalQRYs/tableData.gif This shouldn't be hard!! Thanks adrian, Will ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273249 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Need More SQL help - DRIVING ME NUTS!
No been following this thread, but can you do the first two parts? "Show me a list of all the questions. With each question, I need all possible answers" What about the third part? "With each possible answer, show me a count of the ones that were selected" Do those then try to combine them. Adrian -Original Message- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: 21 March 2007 10:45 To: CF-Talk Subject: Re: Need More SQL help - DRIVING ME NUTS! Put every field from the select that is not an aggregate in your >group by and you get something understandable. > >Jochem Jochem, I did this and it still gives me incorrect results. It gives me a count of 1 for each answer, no matter what the answer is. I'm just testing with one evaluation record to keep it simple. I should still get 0 records returned for some of these answers. I don't know why I can't simply run a query that says this: "Show me a list of all the questions. With each question, I need all possible answers. With each possible answer, show me a count of the ones that were selected" I should be able to get this with a query, but I'm not. I'm missing something. ARG!! Will ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273246 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need More SQL help - DRIVING ME NUTS!
Put every field from the select that is not an aggregate in your >group by and you get something understandable. > >Jochem Jochem, I did this and it still gives me incorrect results. It gives me a count of 1 for each answer, no matter what the answer is. I'm just testing with one evaluation record to keep it simple. I should still get 0 records returned for some of these answers. I don't know why I can't simply run a query that says this: "Show me a list of all the questions. With each question, I need all possible answers. With each possible answer, show me a count of the ones that were selected" I should be able to get this with a query, but I'm not. I'm missing something. ARG!! Will ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273245 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need More SQL help - DRIVING ME NUTS!
Will Tomlinson wrote: > And what I can't figure out is why this: > > COUNT(EAR.answerID) AS numAnswers > > would produce values of 1 for rows in which it should be 0. Because you are using MySQL and MySQL has broken grouping. Your statement is invalid SQL and the database should throw an error, but MySQL pretends to know what you want and gives you back some random data. Put every field from the select that is not an aggregate in your group by and you get something understandable. Jochem ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273236 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need More SQL help - DRIVING ME NUTS!
And what I can't figure out is why this: COUNT(EAR.answerID) AS numAnswers would produce values of 1 for rows in which it should be 0. Thanks, Will ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273233 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need More SQL help - DRIVING ME NUTS!
MySQL 5 Here's some table date as it looks in tblEvalAnswerResults. This is a many table. http://wtomlinson.com/evalQRYs/tableData.gif Then here's what my query produces. I had to add E.evalID to get both evaluations to show up in the results. I went ahead and did another evaluation, so there are two tblEvaluations records, 3 questions each, which produced 6 records in the tblEvalAnswerResults many table. http://wtomlinson.com/evalQRYs/qryDatabig.gif How I should fix this query to give me what I need, I have no idea. I simply want to display each question, along with its possible answers, and a count of the answers that were chosen. All in the same query output in rows, like me link from the first post. Thanks, hope this makes sense. Will ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273232 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need More SQL help - DRIVING ME NUTS!
Will, Which DB are you using and if it's not difficult can you also post db scripts to create tables and populate some sample data.? Thanks Qasim On 3/20/07, Will Tomlinson <[EMAIL PROTECTED]> wrote: > > Ok, I'm really close on this. I've been workin on it for hours, changing > queries, not gettin the results I need. > > I filled out one survey of 3 questions, each question has 5 possible > answers. > > Here's the query: > > SELECT > Q.questionID, > Q.questionsetID, > Q.question, > Q.answersetid, > ARS.answersetid, > ARS.answersetname, > A.answerID, > A.answersetID, > A.answername, > A.answersortnum, > A.answervalue, > EAR.evalID, > EAR.answerID, > E.evalID, > E.InstructorID, > COUNT(EAR.answerID) AS numAnswers > FROM tblquestions Q > INNER JOIN tblanswersets ARS USING (answerSetID) > INNER JOIN tblanswers A USING (answerSetID) > LEFT JOIN tblevalanswerresults EAR USING (questionID) > INNER JOIN tblEvaluations E > ON E.evalID = EAR.evalID > WHERE E.InstructorID = '#SESSION.CollegeID#' > GROUP BY Q.questionID, A.answerID > > HEre's the results, with my > > http://wtomlinson.com/evalQRYs/qryData2.gif > > It should return 0 for 4 answers out of 5, since I can only answer one of > five possible answers. But it's counting 1 for each answer - not right. I > think I can tell what's happening, but don't know how to fix it. It's like > it's grouping off the tblAnswers table, but should be calculating rows from > the tblEvalAnswerResults table. > > And here's my DB schema if it helps: > > http://wtomlinson.com/evalDBSchema.jpg > > Thanks!! > > Will > > ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273231 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Need More SQL help - DRIVING ME NUTS!
Ok, I'm really close on this. I've been workin on it for hours, changing queries, not gettin the results I need. I filled out one survey of 3 questions, each question has 5 possible answers. Here's the query: SELECT Q.questionID, Q.questionsetID, Q.question, Q.answersetid, ARS.answersetid, ARS.answersetname, A.answerID, A.answersetID, A.answername, A.answersortnum, A.answervalue, EAR.evalID, EAR.answerID, E.evalID, E.InstructorID, COUNT(EAR.answerID) AS numAnswers FROM tblquestions Q INNER JOIN tblanswersets ARS USING (answerSetID) INNER JOIN tblanswers A USING (answerSetID) LEFT JOIN tblevalanswerresults EAR USING (questionID) INNER JOIN tblEvaluations E ON E.evalID = EAR.evalID WHERE E.InstructorID = '#SESSION.CollegeID#' GROUP BY Q.questionID, A.answerID HEre's the results, with my http://wtomlinson.com/evalQRYs/qryData2.gif It should return 0 for 4 answers out of 5, since I can only answer one of five possible answers. But it's counting 1 for each answer - not right. I think I can tell what's happening, but don't know how to fix it. It's like it's grouping off the tblAnswers table, but should be calculating rows from the tblEvalAnswerResults table. And here's my DB schema if it helps: http://wtomlinson.com/evalDBSchema.jpg Thanks!! Will ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273230 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need some SQL help!
Ok, I ALMOST got it! The results that are incorrect is just the number of answers per question. Here's what the query returns: http://wtomlinson.com/evalQRYs/qryData.jpg Look at numAnswers. I completed two evaluations. But numAnswers should be the total count of each answer that was given. I obviously didn't answer twice for every answer as shown in the query results. I need it to count up the number of answers that were chosen, for each answer, for each question. Here's my query as it stands now: ALMOST! SELECT Q.questionID, Q.questionsetID, Q.question, Q.answersetid, ARS.answersetid, ARS.answersetname, A.answerID, A.answersetID, A.answername, A.answersortnum, A.answervalue, EAR.evalID, EAR.answerID, E.evalID, E.InstructorID, COUNT(EAR.answerID) AS numAnswers FROM tblquestions Q INNER JOIN tblanswersets ARS USING (answerSetID) INNER JOIN tblanswers A USING (answerSetID) LEFT JOIN tblevalanswerresults EAR USING (questionID) INNER JOIN tblEvaluations E ON E.evalID = EAR.evalID WHERE E.InstructorID = '1021338' GROUP BY Q.questionID, A.answerID Thanks, Will ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273191 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need some SQL help!
I may have figured it out... I used this for my group by. GROUP BY Q.questionID, A.answerID Still needta test it more tho but my results are lookin better. Thanks Jochem! Will ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273182 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need some SQL help!
Jochem, this is close but not quite giving me the results I'm after. Here's the data as it looks in the table. I answered three questions, each had a choice of 5 answers. I chose 1 answer for each question. Here's the data as it appears in the tblEvalAnswerResults many table. http://wtomlinson.com/evalQRYs/tableData.jpg Here's the qry: SELECT Q.questionID, Q.questionsetID, Q.question, Q.answersetid, ARS.answersetid, ARS.answersetname, A.answersetID, A.answername, A.answersortnum, A.answervalue, EAR.evalID, EAR.answerID, A.answerID, COUNT(EAR.answerID) FROM tblquestions Q INNER JOIN tblanswersets ARS USING (answerSetID) INNER JOIN tblanswers A USING (answerSetID) LEFT JOIN tblevalanswerresults EAR USING (answerID) GROUP BY A.answerID Here's the data the query returns: http://wtomlinson.com/evalQRYs/qryData.jpg Notice how it's indeed returning 5 rows - 1 per answer choice. But It's also just returning 1 question. There were 3 questions answered. I've played with it some and will keep trying. Any ideas? Thanks much, Will ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273179 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need some SQL help!
>Will Tomlinson wrote: >> >> Do all these individual queries go into one query, separated with a ; ? > >Just run the last one, the others are to show how you build it step by step. > duhh.. :) Thanks Jochem! Will ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273121 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need some SQL help!
Will Tomlinson wrote: > > Do all these individual queries go into one query, separated with a ; ? Just run the last one, the others are to show how you build it step by step. Jochem ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273113 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4