Re: Why doesn't this query return more than 1 record?
On Monday 03 October 2005 17:29, Rick Faircloth wrote: And...I use cfqueryparam on all my inserts and updates, but what's the rationale for using them on selects? Because of URL manipulation? And it should speed them up too. -- Tom Chiverton Advanced ColdFusion Programmer ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219997 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Why doesn't this query return more than 1 record?
I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219891 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Why doesn't this query return more than 1 record?
Is your clientID really of some other type than numeric (you've got it in quotes) ? You should also be using cfquerypapram. (snicker) Rick Faircloth wrote: I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219894 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Why doesn't this query return more than 1 record?
how are you determining the # of records returned? On 10/3/05, Rick Faircloth [EMAIL PROTECTED] wrote: I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219895 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
Well how many rows do you expect back? It has to be down to your WHERE clause. Have a look in the tables and see whether more rows fit the criteria you're using. Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 16:47 To: CF-Talk Subject: Why doesn't this query return more than 1 record? I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219899 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Why doesn't this query return more than 1 record?
My first instinct is to say... cause thats whats in the table. Try doing a select FG.*, C.* That should show the differences between the two rows and possibly shed some light on the situation. Which db is this? ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219904 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
Yep...Client_ID (with the underscore for readability) is an integer... And...I use cfqueryparam on all my inserts and updates, but what's the rationale for using them on selects? Because of URL manipulation? Rick -Original Message- From: Ray Champagne [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:50 AM To: CF-Talk Subject: Re: Why doesn't this query return more than 1 record? Is your clientID really of some other type than numeric (you've got it in quotes) ? You should also be using cfquerypapram. (snicker) Rick Faircloth wrote: I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219911 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
I run the production server here and work on the site straight on the server and just look at the output...there should be 4 records returned...they're in the database... Rick -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:51 AM To: CF-Talk Subject: Re: Why doesn't this query return more than 1 record? how are you determining the # of records returned? On 10/3/05, Rick Faircloth [EMAIL PROTECTED] wrote: I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219912 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
It should return 4 records, which are the only ones in the database / table that match...it's a database that I'm developing as I develop the site... I've been over the data at the db, and over the code multiple times and must just be missing something... It's returning only the first record each time...and that's the record that matches the URL.Client_ID...but this code should return more than one record, right? Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:58 AM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Well how many rows do you expect back? It has to be down to your WHERE clause. Have a look in the tables and see whether more rows fit the criteria you're using. Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 16:47 To: CF-Talk Subject: Why doesn't this query return more than 1 record? I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219915 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
Tried the FG.*, C.*...no go...same results... -Original Message- From: Anthony Prato [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 12:02 PM To: CF-Talk Subject: Re: Why doesn't this query return more than 1 record? My first instinct is to say... cause thats whats in the table. Try doing a select FG.*, C.* That should show the differences between the two rows and possibly shed some light on the situation. Which db is this? ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219916 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Why doesn't this query return more than 1 record?
you're looking at records returned thru CF? thru the database itself (via something like enterprise manager or query analyzer)? if the former, using queryname.recordcount? cfoutput query=queryname? cfloop query=queryname? On 10/3/05, Rick Faircloth [EMAIL PROTECTED] wrote: I run the production server here and work on the site straight on the server and just look at the output...there should be 4 records returned...they're in the database... Rick -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:51 AM To: CF-Talk Subject: Re: Why doesn't this query return more than 1 record? how are you determining the # of records returned? On 10/3/05, Rick Faircloth [EMAIL PROTECTED] wrote: I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219917 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
Now don't get mad when I suggest this, but how do you know it's only returning 1 row? Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 17:34 To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? It should return 4 records, which are the only ones in the database / table that match...it's a database that I'm developing as I develop the site... I've been over the data at the db, and over the code multiple times and must just be missing something... It's returning only the first record each time...and that's the record that matches the URL.Client_ID...but this code should return more than one record, right? Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:58 AM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Well how many rows do you expect back? It has to be down to your WHERE clause. Have a look in the tables and see whether more rows fit the criteria you're using. Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 16:47 To: CF-Talk Subject: Why doesn't this query return more than 1 record? I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219919 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
Hi, If you script the table and data, and post it, we could all play. Ian -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 17:45 To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Now don't get mad when I suggest this, but how do you know it's only returning 1 row? Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 17:34 To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? It should return 4 records, which are the only ones in the database / table that match...it's a database that I'm developing as I develop the site... I've been over the data at the db, and over the code multiple times and must just be missing something... It's returning only the first record each time...and that's the record that matches the URL.Client_ID...but this code should return more than one record, right? Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:58 AM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Well how many rows do you expect back? It has to be down to your WHERE clause. Have a look in the tables and see whether more rows fit the criteria you're using. Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 16:47 To: CF-Talk Subject: Why doesn't this query return more than 1 record? I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219921 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Why doesn't this query return more than 1 record?
Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' It's returning only the first record each time...and that's the record that matches the URL.Client_ID...but this code should return more than one record, right? well that depends how many FG.Group_ID match the C.Client_Family_ID in the Clients record that matches the URL.Client_ID supplied. Could you dump a sample of the data here for us to see? Mike T ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219924 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Why doesn't this query return more than 1 record?
How many records are in each table and what do they look like (just the columns in question). The only way I'd see you getting more than one record back is if there are several different records in the clients table with the same client_id. I'm guessing the tables look similar to this: CLIENTS: client_id 1 2 3 client_family_id 123 456 789 FAMILY_GROUPS group_id 123 456 789 group_name smith james johnson So, when your url.client_id variable equals 1, you're going to get client_id = 1, fg.group_id = 123, fg.group_name = smith Am I missing something here Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219928 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
Yes, I'm looking at the results returned on the website I'm building... however, using Navicat for the query, still only 1 record is returned. My logic must just be wrong in using the C.Client_ID = '#URL.Client_ID#'. There's only 1 record where that is true... Maybe not enough caffeine... Rick -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 12:40 PM To: CF-Talk Subject: Re: Why doesn't this query return more than 1 record? you're looking at records returned thru CF? thru the database itself (via something like enterprise manager or query analyzer)? if the former, using queryname.recordcount? cfoutput query=queryname? cfloop query=queryname? On 10/3/05, Rick Faircloth [EMAIL PROTECTED] wrote: I run the production server here and work on the site straight on the server and just look at the output...there should be 4 records returned...they're in the database... Rick -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:51 AM To: CF-Talk Subject: Re: Why doesn't this query return more than 1 record? how are you determining the # of records returned? On 10/3/05, Rick Faircloth [EMAIL PROTECTED] wrote: I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219930 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
Oh, I'm so mad! ;o) I'm returning the results to the website and I've also run the query on Navicat... Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 12:45 PM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Now don't get mad when I suggest this, but how do you know it's only returning 1 row? Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 17:34 To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? It should return 4 records, which are the only ones in the database / table that match...it's a database that I'm developing as I develop the site... I've been over the data at the db, and over the code multiple times and must just be missing something... It's returning only the first record each time...and that's the record that matches the URL.Client_ID...but this code should return more than one record, right? Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:58 AM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Well how many rows do you expect back? It has to be down to your WHERE clause. Have a look in the tables and see whether more rows fit the criteria you're using. Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 16:47 To: CF-Talk Subject: Why doesn't this query return more than 1 record? I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219932 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
Thought you might be doing something like: cfoutput #qYourQuery.SomeRow# /cfoutput and only seeing one row, namely the first one. Have you solved it? Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 18:10 To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Oh, I'm so mad! ;o) I'm returning the results to the website and I've also run the query on Navicat... Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 12:45 PM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Now don't get mad when I suggest this, but how do you know it's only returning 1 row? Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 17:34 To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? It should return 4 records, which are the only ones in the database / table that match...it's a database that I'm developing as I develop the site... I've been over the data at the db, and over the code multiple times and must just be missing something... It's returning only the first record each time...and that's the record that matches the URL.Client_ID...but this code should return more than one record, right? Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:58 AM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Well how many rows do you expect back? It has to be down to your WHERE clause. Have a look in the tables and see whether more rows fit the criteria you're using. Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 16:47 To: CF-Talk Subject: Why doesn't this query return more than 1 record? I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219933 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
Ok...
Clients Table
--
Client_ID
Client_Family_ID (FK for Family_Groups Table)
etc...
Family_Groups Table
--
Group_ID
There are 4 records in the Clients table...all have
the same Client_Family_ID, which is 1...
There is 1 record in the Family_Groups table,
which has the Family_Group_ID of 1...
Link is clicked, sending Client_ID = 1...(that client
has a Client_Family_ID FK that = 1
So, give me all the records where the Family_Group_ID = Client_Family_ID
and the Client_ID = 1 ('#URL.Client_ID#')...
I think that Client_ID = '#URL.Client_ID#' is going to
limit the records to 1...
But how do I get all Clients that have Family_Group_ID's
that match the Family_Group_ID of the Client specified by the
'#URL.Client_ID#' ???
I know I've been away from coding for a couple of months...but sheesh!
Rick
-Original Message-
From: Ken Ferguson [mailto:[EMAIL PROTECTED]
Sent: Monday, October 03, 2005 1:02 PM
To: CF-Talk
Subject: Re: Why doesn't this query return more than 1 record?
How many records are in each table and what do they look like (just the
columns in question). The only way I'd see you getting more than one
record back is if there are several different records in the clients
table with the same client_id. I'm guessing the tables look
similar to this:
CLIENTS:
client_id
1
2
3
client_family_id
123
456
789
FAMILY_GROUPS
group_id
123
456
789
group_name
smith
james
johnson
So, when your url.client_id variable equals 1, you're going to get
client_id = 1, fg.group_id = 123, fg.group_name = smith
Am I missing something here
Select FG.Group_ID, FG.Group_Name,
C.Client_ID,
C.Client_First_Name, C.Client_Middle_Name,
C.Client_Last_Name
from Family_Groups FG, Clients C
where FG.Group_ID = C.Client_Family_ID
and C.Client_ID = '#URL.Client_ID#'
Rick
~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219938
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
No...I'm looping the query on the website... I think I've gone insane...this should be so simple. Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 1:23 PM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Thought you might be doing something like: cfoutput #qYourQuery.SomeRow# /cfoutput and only seeing one row, namely the first one. Have you solved it? Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 18:10 To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Oh, I'm so mad! ;o) I'm returning the results to the website and I've also run the query on Navicat... Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 12:45 PM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Now don't get mad when I suggest this, but how do you know it's only returning 1 row? Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 17:34 To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? It should return 4 records, which are the only ones in the database / table that match...it's a database that I'm developing as I develop the site... I've been over the data at the db, and over the code multiple times and must just be missing something... It's returning only the first record each time...and that's the record that matches the URL.Client_ID...but this code should return more than one record, right? Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:58 AM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Well how many rows do you expect back? It has to be down to your WHERE clause. Have a look in the tables and see whether more rows fit the criteria you're using. Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 16:47 To: CF-Talk Subject: Why doesn't this query return more than 1 record? I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219939 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Why doesn't this query return more than 1 record?
Well, you haven't posted any data snapshot out here yet, but I'm betting you're getting what you should be getting back. Basically, what you're saying is this: Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name From Clients C inner join Family_Groups FG on C.Client_Family_ID = FG.Group_ID Where C.Client_ID = '#URL.Client_ID#' I don't see any way that this is going to (sensibly) return you more than one record!!! You are effectively limiting it to one record with your where clause. If you feel that I'm wrong about this, please post your data out here so we can take a look at what is happening. --Ferg Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219940 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Why doesn't this query return more than 1 record?
There you go now.
Select * from client where client_family_id = (
select client_family_id from client where client_id = #url.clientID#
)
would be one way.
Rick Faircloth wrote:
Ok...
Clients Table
--
Client_ID
Client_Family_ID (FK for Family_Groups Table)
etc...
Family_Groups Table
--
Group_ID
There are 4 records in the Clients table...all have
the same Client_Family_ID, which is 1...
There is 1 record in the Family_Groups table,
which has the Family_Group_ID of 1...
Link is clicked, sending Client_ID = 1...(that client
has a Client_Family_ID FK that = 1
So, give me all the records where the Family_Group_ID = Client_Family_ID
and the Client_ID = 1 ('#URL.Client_ID#')...
I think that Client_ID = '#URL.Client_ID#' is going to
limit the records to 1...
But how do I get all Clients that have Family_Group_ID's
that match the Family_Group_ID of the Client specified by the
'#URL.Client_ID#' ???
I know I've been away from coding for a couple of months...but sheesh!
Rick
-Original Message-
From: Ken Ferguson [mailto:[EMAIL PROTECTED]
Sent: Monday, October 03, 2005 1:02 PM
To: CF-Talk
Subject: Re: Why doesn't this query return more than 1 record?
How many records are in each table and what do they look like (just the
columns in question). The only way I'd see you getting more than one
record back is if there are several different records in the clients
table with the same client_id. I'm guessing the tables look
similar to this:
CLIENTS:
client_id
1
2
3
client_family_id
123
456
789
FAMILY_GROUPS
group_id
123
456
789
group_name
smith
james
johnson
So, when your url.client_id variable equals 1, you're going to get
client_id = 1, fg.group_id = 123, fg.group_name = smith
Am I missing something here
Select FG.Group_ID, FG.Group_Name,
C.Client_ID,
C.Client_First_Name, C.Client_Middle_Name,
C.Client_Last_Name
from Family_Groups FG, Clients C
where FG.Group_ID = C.Client_Family_ID
and C.Client_ID = '#URL.Client_ID#'
Rick
~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219941
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
Depending on your join, missing data can cause a row you expect to see not be part of the query result set. Mark -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 1:45 PM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? No...I'm looping the query on the website... I think I've gone insane...this should be so simple. Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 1:23 PM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Thought you might be doing something like: cfoutput #qYourQuery.SomeRow# /cfoutput and only seeing one row, namely the first one. Have you solved it? Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 18:10 To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Oh, I'm so mad! ;o) I'm returning the results to the website and I've also run the query on Navicat... Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 12:45 PM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Now don't get mad when I suggest this, but how do you know it's only returning 1 row? Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 17:34 To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? It should return 4 records, which are the only ones in the database / table that match...it's a database that I'm developing as I develop the site... I've been over the data at the db, and over the code multiple times and must just be missing something... It's returning only the first record each time...and that's the record that matches the URL.Client_ID...but this code should return more than one record, right? Rick -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:58 AM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Well how many rows do you expect back? It has to be down to your WHERE clause. Have a look in the tables and see whether more rows fit the criteria you're using. Ade -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: 03 October 2005 16:47 To: CF-Talk Subject: Why doesn't this query return more than 1 record? I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219942 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Why doesn't this query return more than 1 record?
Sorry, that last one was bad:
Select c.*, fg.*
From clients c inner join family_groups fg on
c.client_family_id = fg.group_id
Where c.family_group_id = (
select family_group_id from clients where client_id = #url.client_id#
)
Rick Faircloth wrote:
Ok...
Clients Table
--
Client_ID
Client_Family_ID (FK for Family_Groups Table)
etc...
Family_Groups Table
--
Group_ID
There are 4 records in the Clients table...all have
the same Client_Family_ID, which is 1...
There is 1 record in the Family_Groups table,
which has the Family_Group_ID of 1...
Link is clicked, sending Client_ID = 1...(that client
has a Client_Family_ID FK that = 1
So, give me all the records where the Family_Group_ID = Client_Family_ID
and the Client_ID = 1 ('#URL.Client_ID#')...
I think that Client_ID = '#URL.Client_ID#' is going to
limit the records to 1...
But how do I get all Clients that have Family_Group_ID's
that match the Family_Group_ID of the Client specified by the
'#URL.Client_ID#' ???
I know I've been away from coding for a couple of months...but sheesh!
Rick
-Original Message-
From: Ken Ferguson [mailto:[EMAIL PROTECTED]
Sent: Monday, October 03, 2005 1:02 PM
To: CF-Talk
Subject: Re: Why doesn't this query return more than 1 record?
How many records are in each table and what do they look like (just the
columns in question). The only way I'd see you getting more than one
record back is if there are several different records in the clients
table with the same client_id. I'm guessing the tables look
similar to this:
CLIENTS:
client_id
1
2
3
client_family_id
123
456
789
FAMILY_GROUPS
group_id
123
456
789
group_name
smith
james
johnson
So, when your url.client_id variable equals 1, you're going to get
client_id = 1, fg.group_id = 123, fg.group_name = smith
Am I missing something here
Select FG.Group_ID, FG.Group_Name,
C.Client_ID,
C.Client_First_Name, C.Client_Middle_Name,
C.Client_Last_Name
from Family_Groups FG, Clients C
where FG.Group_ID = C.Client_Family_ID
and C.Client_ID = '#URL.Client_ID#'
Rick
~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219943
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
If Client_ID is your PK, and you're restricting it in the AND clause, that's
enough to limit it to one.
Ade
-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: 03 October 2005 18:44
To: CF-Talk
Subject: RE: Why doesn't this query return more than 1 record?
Ok...
Clients Table
--
Client_ID
Client_Family_ID (FK for Family_Groups Table)
etc...
Family_Groups Table
--
Group_ID
There are 4 records in the Clients table...all have
the same Client_Family_ID, which is 1...
There is 1 record in the Family_Groups table,
which has the Family_Group_ID of 1...
Link is clicked, sending Client_ID = 1...(that client
has a Client_Family_ID FK that = 1
So, give me all the records where the Family_Group_ID = Client_Family_ID
and the Client_ID = 1 ('#URL.Client_ID#')...
I think that Client_ID = '#URL.Client_ID#' is going to
limit the records to 1...
But how do I get all Clients that have Family_Group_ID's
that match the Family_Group_ID of the Client specified by the
'#URL.Client_ID#' ???
I know I've been away from coding for a couple of months...but sheesh!
Rick
-Original Message-
From: Ken Ferguson [mailto:[EMAIL PROTECTED]
Sent: Monday, October 03, 2005 1:02 PM
To: CF-Talk
Subject: Re: Why doesn't this query return more than 1 record?
How many records are in each table and what do they look like (just the
columns in question). The only way I'd see you getting more than one
record back is if there are several different records in the clients
table with the same client_id. I'm guessing the tables look
similar to this:
CLIENTS:
client_id
1
2
3
client_family_id
123
456
789
FAMILY_GROUPS
group_id
123
456
789
group_name
smith
james
johnson
So, when your url.client_id variable equals 1, you're going to get
client_id = 1, fg.group_id = 123, fg.group_name = smith
Am I missing something here
Select FG.Group_ID, FG.Group_Name,
C.Client_ID,
C.Client_First_Name, C.Client_Middle_Name,
C.Client_Last_Name
from Family_Groups FG, Clients C
where FG.Group_ID = C.Client_Family_ID
and C.Client_ID = '#URL.Client_ID#'
Rick
~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219944
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
Depending on your join type, missing data can cause a row you expect to see not be part of the query result set. Mark ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219945 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Why doesn't this query return more than 1 record?
Our oracle databases are case sensitive, make sure the values are EXACT
matches on case and no trailing spaces, etc. When in doubt you can trim
and upper to ensure exact matches.
Thanks,
CC
x56927
This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit
written agreement or government initiative expressly permitting the use of
e-mail for such purpose.
Ken Ferguson ken
@latitudetech.net
10/03/2005 10:57 AM
Please respond to cf-talk
To: CF-Talk cf-talk@houseoffusion.com
cc:
Subject:Re: Why doesn't this query return more than 1
record?
Sorry, that last one was bad:
Select c.*, fg.*
From clients c inner join family_groups fg on
c.client_family_id = fg.group_id
Where c.family_group_id = (
select family_group_id from clients where client_id = #url.client_id#
)
Rick Faircloth wrote:
Ok...
Clients Table
--
Client_ID
Client_Family_ID (FK for Family_Groups Table)
etc...
Family_Groups Table
--
Group_ID
There are 4 records in the Clients table...all have
the same Client_Family_ID, which is 1...
There is 1 record in the Family_Groups table,
which has the Family_Group_ID of 1...
Link is clicked, sending Client_ID = 1...(that client
has a Client_Family_ID FK that = 1
So, give me all the records where the Family_Group_ID = Client_Family_ID
and the Client_ID = 1 ('#URL.Client_ID#')...
I think that Client_ID = '#URL.Client_ID#' is going to
limit the records to 1...
But how do I get all Clients that have Family_Group_ID's
that match the Family_Group_ID of the Client specified by the
'#URL.Client_ID#' ???
I know I've been away from coding for a couple of months...but sheesh!
Rick
-Original Message-
From: Ken Ferguson [mailto:[EMAIL PROTECTED]
Sent: Monday, October 03, 2005 1:02 PM
To: CF-Talk
Subject: Re: Why doesn't this query return more than 1 record?
How many records are in each table and what do they look like (just the
columns in question). The only way I'd see you getting more than one
record back is if there are several different records in the clients
table with the same client_id. I'm guessing the tables look
similar to this:
CLIENTS:
client_id
1
2
3
client_family_id
123
456
789
FAMILY_GROUPS
group_id
123
456
789
group_name
smith
james
johnson
So, when your url.client_id variable equals 1, you're going to get
client_id = 1, fg.group_id = 123, fg.group_name = smith
Am I missing something here
Select FG.Group_ID, FG.Group_Name,
C.Client_ID,
C.Client_First_Name, C.Client_Middle_Name,
C.Client_Last_Name
from Family_Groups FG, Clients C
where FG.Group_ID = C.Client_Family_ID
and C.Client_ID = '#URL.Client_ID#'
Rick
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219946
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record?
To answer your question about cfqueryparam...from what I heard last year at MAX, using cfqueryparam not only protects you from url/form manipulation, but it also increases performance. When the query is first passed, a query map is laid out so the DB knows what it's going to have to do. When you do a normal select, this map needs to be laid out each time a new query is passed even if it's almost identical to an earlier query. When you use cfqueryparam, it lays out the map once and just passes in the parameters. John Burns Certified Advanced ColdFusion MX Developer Wyle Laboratories, Inc. | Web Developer -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 12:29 PM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Yep...Client_ID (with the underscore for readability) is an integer... And...I use cfqueryparam on all my inserts and updates, but what's the rationale for using them on selects? Because of URL manipulation? Rick -Original Message- From: Ray Champagne [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:50 AM To: CF-Talk Subject: Re: Why doesn't this query return more than 1 record? Is your clientID really of some other type than numeric (you've got it in quotes) ? You should also be using cfquerypapram. (snicker) Rick Faircloth wrote: I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219947 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Why doesn't this query return more than 1 record? ---Solved
You were on the right track, Ken...just got the variables mixed up, which was my fault, really...my FK in clients was not named well according to the PK in the family_groups table. Anyway...it did require an inner join as you have below (yuck, I hate joins...), because I'm joining data from two tables instead of just pulling data from one table based on relationships...at least that's how I understand it... Thanks for everyone's help! Rick Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name From Clients C Inner Join Family_Groups FG On C.Client_Family_ID = FG.Group_ID Where C.Client_Family_ID = (Select Client_Family_ID From Clients Where Client_ID = '#URL.Client_ID#') -Original Message- From: Ken Ferguson [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 1:58 PM To: CF-Talk Subject: Re: Why doesn't this query return more than 1 record? Sorry, that last one was bad: Select c.*, fg.* From clients c inner join family_groups fg on c.client_family_id = fg.group_id Where c.family_group_id = ( select family_group_id from clients where client_id = #url.client_id# ) ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219948 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SPAM-LOW: RE: Why doesn't this query return more than 1 record?
Interesting...enhanced protection and performance...I shall use it with all my select variables, henceforth! Thanks! Rick -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 2:31 PM To: CF-Talk Subject: SPAM-LOW: RE: Why doesn't this query return more than 1 record? To answer your question about cfqueryparam...from what I heard last year at MAX, using cfqueryparam not only protects you from url/form manipulation, but it also increases performance. When the query is first passed, a query map is laid out so the DB knows what it's going to have to do. When you do a normal select, this map needs to be laid out each time a new query is passed even if it's almost identical to an earlier query. When you use cfqueryparam, it lays out the map once and just passes in the parameters. John Burns Certified Advanced ColdFusion MX Developer Wyle Laboratories, Inc. | Web Developer -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 12:29 PM To: CF-Talk Subject: RE: Why doesn't this query return more than 1 record? Yep...Client_ID (with the underscore for readability) is an integer... And...I use cfqueryparam on all my inserts and updates, but what's the rationale for using them on selects? Because of URL manipulation? Rick -Original Message- From: Ray Champagne [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 11:50 AM To: CF-Talk Subject: Re: Why doesn't this query return more than 1 record? Is your clientID really of some other type than numeric (you've got it in quotes) ? You should also be using cfquerypapram. (snicker) Rick Faircloth wrote: I'm not dumb, I'm not dumb, I'm not dumb. If I repeat it often enough, it has to be true, right? Anyway...why won't this query return more than 1 record? Select FG.Group_ID, FG.Group_Name, C.Client_ID, C.Client_First_Name, C.Client_Middle_Name, C.Client_Last_Name from Family_Groups FG, Clients C where FG.Group_ID = C.Client_Family_ID and C.Client_ID = '#URL.Client_ID#' Rick ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219949 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SPAM-LOW: RE: Why doesn't this query return more than 1 record?
Interesting...enhanced protection and performance...I shall use it with all my select variables, henceforth! Thanks! Rick Don't forget your INSERT and UPDATE variables. One should use queryparam with any type of query that accepts user input for values in the query. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219954 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SPAM-LOW: RE: Why doesn't this query return more than 1 record?
Thanks, Ian...I already use them for the inserts and updates...just didn't think there was much of an issue when using them for URL variables in queries. Rick -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 3:22 PM To: CF-Talk Subject: RE: SPAM-LOW: RE: Why doesn't this query return more than 1 record? Interesting...enhanced protection and performance...I shall use it with all my select variables, henceforth! Thanks! Rick Don't forget your INSERT and UPDATE variables. One should use queryparam with any type of query that accepts user input for values in the query. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219959 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SPAM-LOW: RE: Why doesn't this query return more than 1 record?
Thanks, Ian...I already use them for the inserts and updates...just didn't think there was much of an issue when using them for URL variables in queries. Rick .and that's when you REALLY need them form a security standpoint. Of course with SQL Server and Oracle (and most likley some other DBs), they also auto-handle BIND variables and speed up query processing. Bryan Stevenson B.Comm. VP Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: [EMAIL PROTECTED] web: www.electricedgesystems.com ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219960 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
