late night query ?
i have a query thats uses a userid to get items out of a db that have a matching userID and i need to run another query thats uses those numbers to get descriptions outta another table the basic query is this cfquery name=qCards datasource=#dsn# username=#un# password=#pw# SELECT cardID FROM userscards WHERE usersID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer /cfquery the other tables is called tblcards and i need to match the list from the 1st query to get all the descriptions from this one from a field called CardID brain is mush, any help? tia [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: late night query ?
dave. What database are you using? try.. SELECT usercards.cardID, tblcards.cardID WHERE usercards.cardID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer AND usercards.cardID = tblcards.cardID Ricardo. On Fri, 15 Oct 2004 02:09:15 -0400, dave [EMAIL PROTECTED] wrote: i have a query thats uses a userid to get items out of a db that have a matching userID and i need to run another query thats uses those numbers to get descriptions outta another table the basic query is this cfquery name=qCards datasource=#dsn# username=#un# password=#pw# SELECT cardID FROM userscards WHERE usersID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer /cfquery the other tables is called tblcards and i need to match the list from the 1st query to get all the descriptions from this one from a field called CardID brain is mush, any help? tia [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: late night query ?
If you don't need the results from the first query, do a join SELECT c.whatever, c.somethingelse, c.cardID FROM usercards uc, tblcards c WHERE uc.cardID = c.cardID AND uc.usersID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer If you need the first query, make a second query if the first returned something: SELECT c.whatever, c.somethingelse, c.cardID FROM tblcards c WHERE c.cardID IN (cfqueryparam value=#ValueList(qCards.cardID)# cfsqltype=cf_sql_integer list=yes) Pascal -Original Message- From: dave [mailto:[EMAIL PROTECTED] Sent: 15 October 2004 08:09 To: CF-Talk Subject: late night query ? i have a query thats uses a userid to get items out of a db that have a matching userID and i need to run another query thats uses those numbers to get descriptions outta another table the basic query is this cfquery name=qCards datasource=#dsn# username=#un# password=#pw# SELECT cardID FROM userscards WHERE usersID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer /cfquery the other tables is called tblcards and i need to match the list from the 1st query to get all the descriptions from this one from a field called CardID brain is mush, any help? tia [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: late night query ?
i love it when i make it more complicated then it really iz lol using mysql, its close but i get this error Error Executing Database Query. Syntax error or access violation: 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 'WHERE usercards.usersID = 1 AND usercards.cardID = tblcards.Ca The error occurred in C:\Websites\mtgotradingpos\users\myCards\mycards.cfm: line 24 22 : cfquery name=qCards datasource=#dsn# username=#un# password=#pw# 23 : SELECT usercards.cardID, tblcards.CardID 24 : WHERE usercards.usersID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer 25 : AND usercards.cardID = tblcards.CardID 26 : /cfquery -- Original Message -- From: Ricardo Russon [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date:Fri, 15 Oct 2004 16:24:55 +1000 dave. What database are you using? try.. SELECT usercards.cardID, tblcards.cardID WHERE usercards.cardID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer AND usercards.cardID = tblcards.cardID Ricardo. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: late night query ?
i think the killer here is that this usercards.cardID = tblcards.cardID could be 1 record or 7000, so maybe have to loop it or maybe qoq -- Original Message -- From: Ricardo Russon [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date:Fri, 15 Oct 2004 16:24:55 +1000 dave. What database are you using? try.. SELECT usercards.cardID, tblcards.cardID WHERE usercards.cardID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer AND usercards.cardID = tblcards.cardID Ricardo. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: late night query ?
i been working since 9am (now 1am) so my train of thought is on the booty call i shoulda went and got, lol dammit! ok, here i'll try and explain better 1st i invoke a cfc (heres the meat of it) this gets the logged in usersname and gives me their userid. cfquery name=qgetUser datasource=#dsn# username=#un# password=#pw# SELECT userID FROM users WHERE username = cfqueryparam value=#getAuthUser()# cfsqltype=cf_sql_varchar /cfquery cfreturn qgetUser from there i query a table called userscards, what this does is basically run the userID through and gets all the matches, which gives me an output of card#'s like 4434, 2234, 5567. then i need to run those through another table called tblcards and in the output match all the numbers i got from the above query, so that i can get the rest of the details out of that table ok now i am lost lol [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: late night query ?
7000 records still is peanuts, ... if ... you correctly add indexes and set the primary keys and relationships. I also execute queries with such amounts of records and they still execute beneath the 10 ms. If I hadn't added database functionality I would to have to sit and wait. Micha Schopman Software Engineer Modern Media, Databankweg 12 M, 3821 ALAmersfoort Tel 033-4535377, Fax 033-4535388 KvK Amersfoort 39081679, Rabo 39.48.05.380 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: late night query ?
Have you tried analyzing the execution plan yet (if you are using SQL Server?) to see where the bottlenecks are? Micha Schopman Software Engineer Modern Media, Databankweg 12 M, 3821 ALAmersfoort Tel 033-4535377, Fax 033-4535388 KvK Amersfoort 39081679, Rabo 39.48.05.380 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: late night query ?
You are missing the FROM clause -Original Message- From: dave [mailto:[EMAIL PROTECTED] Sent: 15 October 2004 08:35 To: CF-Talk Subject: Re: late night query ? i love it when i make it more complicated then it really iz lol using mysql, its close but i get this error Error Executing Database Query. Syntax error or access violation: 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 'WHERE usercards.usersID = 1 AND usercards.cardID = tblcards.Ca The error occurred in C:\Websites\mtgotradingpos\users\myCards\mycards.cfm: line 24 22 : cfquery name=qCards datasource=#dsn# username=#un# password=#pw# 23 : SELECT usercards.cardID, tblcards.CardID 24 : WHERE usercards.usersID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer 25 : AND usercards.cardID = tblcards.CardID 26 : /cfquery -- Original Message -- From: Ricardo Russon [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date:Fri, 15 Oct 2004 16:24:55 +1000 dave. What database are you using? try.. SELECT usercards.cardID, tblcards.cardID WHERE usercards.cardID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer AND usercards.cardID = tblcards.cardID Ricardo. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: late night query ?
sorry but that completely doesnt help like i said its mysql, i dont use any m$ crap this isnt even mine just helping, well trying to help -- Original Message -- From: Micha Schopman [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date:Fri, 15 Oct 2004 09:58:52 +0200 Have you tried analyzing the execution plan yet (if you are using SQL Server?) to see where the bottlenecks are? Micha Schopman Software Engineer Modern Media, Databankweg 12 M, 3821 ALAmersfoort Tel 033-4535377, Fax 033-4535388 KvK Amersfoort 39081679, Rabo 39.48.05.380 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: late night query ?
I've been working since 9am too. Pascal PS Of course, here it's only 10am ;) -Original Message- From: dave [mailto:[EMAIL PROTECTED] Sent: 15 October 2004 09:10 To: CF-Talk Subject: RE: late night query ? i been working since 9am (now 1am) so my train of thought is on the booty call i shoulda went and got, lol dammit! ok, here i'll try and explain better 1st i invoke a cfc (heres the meat of it) this gets the logged in usersname and gives me their userid. cfquery name=qgetUser datasource=#dsn# username=#un# password=#pw# SELECT userID FROM users WHERE username = cfqueryparam value=#getAuthUser()# cfsqltype=cf_sql_varchar /cfquery cfreturn qgetUser from there i query a table called userscards, what this does is basically run the userID through and gets all the matches, which gives me an output of card#'s like 4434, 2234, 5567. then i need to run those through another table called tblcards and in the output match all the numbers i got from the above query, so that i can get the rest of the details out of that table ok now i am lost lol [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: late night query ?
lol -- Original Message -- From: Pascal Peters [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date:Fri, 15 Oct 2004 10:05:48 +0200 I've been working since 9am too. Pascal PS Of course, here it's only 10am ;) [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: late night query ?
What have you tried.. ? .. what have you indexed? .. have you checked your primary keys .. etc.. ? Micha Schopman Software Engineer Modern Media, Databankweg 12 M, 3821 ALAmersfoort Tel 033-4535377, Fax 033-4535388 KvK Amersfoort 39081679, Rabo 39.48.05.380 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: late night query ?
ah, come on Pascal we all know thats an Urban Legend, u really dont need that!! -- Original Message -- From: Pascal Peters [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date:Fri, 15 Oct 2004 10:02:59 +0200 You are missing the FROM clause -Original Message- From: dave [mailto:[EMAIL PROTECTED] Sent: 15 October 2004 08:35 To: CF-Talk Subject: Re: late night query ? i love it when i make it more complicated then it really iz lol using mysql, its close but i get this error Error Executing Database Query. Syntax error or access violation: 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 'WHERE usercards.usersID = 1 AND usercards.cardID = tblcards.Ca The error occurred in C:\Websites\mtgotradingpos\users\myCards\mycards.cfm: line 24 22 : cfquery name=qCards datasource=#dsn# username=#un# password=#pw# 23 : SELECT usercards.cardID, tblcards.CardID 24 : WHERE usercards.usersID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer 25 : AND usercards.cardID = tblcards.CardID 26 : /cfquery -- Original Message -- From: Ricardo Russon [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date:Fri, 15 Oct 2004 16:24:55 +1000 dave. What database are you using? try.. SELECT usercards.cardID, tblcards.cardID WHERE usercards.cardID = cfqueryparam value=#guserID# cfsqltype=cf_sql_integer AND usercards.cardID = tblcards.cardID Ricardo. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: late night query ?
this was the answer the from statements usually does help eh! and of course naming the db tables correctly cfquery name=qCards datasource=#dsn# username=#un# password=#pw# SELECT * FROM userscards, tblcards WHERE userscards.usersID = '#qgetUser.userID#' AND userscards.cardID = tblcards.CardID /cfquery thanks guys [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
