House of Fusion
Search over 2,500 ColdFusion resources here
  
Home of the ColdFusion Community

Mailing Lists 		Home /  Groups /  ColdFusion Talk (CF-Talk)

How to change this query from MS Access to MySQL Query

  << Previous Post |  RSS |  Tree View |  Sort Oldest First |  Subscribe to this Group Next >> 

How to change this query from MS Access to MySQL Query

Hi, Stephen. Rick Faircloth 12/12/2002 09:12 AM
Rick, Stephen Moretti 12/12/2002 04:49 AM
> I didn't get an error when I ran your query below using the left join, Stephen Moretti 12/12/2002 04:22 AM
This seems to work...what do you think? Rick Faircloth 12/11/2002 01:56 PM
Thanks, Gordon...works good. Rick Faircloth 12/11/2002 01:41 PM
SELECT  u.* Cary Gordon 12/11/2002 01:25 PM
I didn't get an error when I ran your query below using the left join, Rick Faircloth 12/11/2002 01:24 PM
Oh, brother.  I spent a quite a bit of time learning to handle Rick Faircloth 12/11/2002 01:22 PM
> This query worked with MS Access, but not with MySQL... Stephen Moretti 12/11/2002 01:05 PM
Rick Faircloth wrote: Jochem van Dieten 12/11/2002 01:00 PM
Hi, all. Rick Faircloth 12/11/2002 11:52 AM

12/12/2002 09:12 AM
Author: Rick Faircloth Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19684#99786 Hi, Stephen. Thanks a lot for the help. As usual, I was overcomplicating the logic. What you've proposed makes perfect sense. I was able to greatly simplify a page-full of subqueries based on your examples.  Now mySQL is happy, therefore, I am too!  :o) (I'll just be glad when mySQL 4.1 comes out with support for subqueries... although now it looks like I don't need them as often as I thought) Have a good day! Rick <>< Rick, ----- Excess quoted text cut - see Original Post for more ----- ummmm.... Taking this a stage at a time, cos its early in the morning and I need coffee... Your original query can be simplified to: (You needn't get the client IDs from the client table as there will only be one record returned) Select * FROM Property WHERE PropertyID IN (             Select PropertyID             from Rentals             where Rentals.ClientID  = #App_ClientID#) So with a JOIN that becomes.... SELECT p.* FROM Properties LEFT JOIN  Rentals     ON Properties.PropertyID = Rental.PropertyID WHERE Rental.ClientID = #App_ClientID# You join the two tables by propertyID and you want all the records from property (the LEFT table) where they match the rental records with a clientid = App_ClientID. Select p.* from properties p, rentals r where p.propertyid = r.propertyid and r.clientid = #App_ClientID# Taking the client table out of the query you created and comparing the clientid from rental to App_ClientID will give you the same result.  See which one is most efficient or pick the one you like the most.  Make sure you've.... Hmmm what was I gonna say.... The phone just rang.... No... Can't remember...  Sorry Rick.  Hope the rest is useful/helpful. Regards Stephen
12/12/2002 04:49 AM
Author: Stephen Moretti Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19684#99753 Rick, ----- Excess quoted text cut - see Original Post for more ----- ummmm.... Taking this a stage at a time, cos its early in the morning and I need coffee... Your original query can be simplified to: (You needn't get the client IDs from the client table as there will only be one record returned) Select * FROM Property WHERE PropertyID IN (             Select PropertyID             from Rentals             where Rentals.ClientID  = #App_ClientID#) So with a JOIN that becomes.... SELECT p.* FROM Properties LEFT JOIN  Rentals     ON Properties.PropertyID = Rental.PropertyID WHERE Rental.ClientID = #App_ClientID# You join the two tables by propertyID and you want all the records from property (the LEFT table) where they match the rental records with a clientid = App_ClientID. Select p.* from properties p, rentals r where p.propertyid = r.propertyid and r.clientid = #App_ClientID# Taking the client table out of the query you created and comparing the clientid from rental to App_ClientID will give you the same result.  See which one is most efficient or pick the one you like the most.  Make sure you've.... Hmmm what was I gonna say.... The phone just rang.... No... Can't remember...  Sorry Rick.  Hope the rest is useful/helpful. Regards Stephen
12/12/2002 04:22 AM
Author: Stephen Moretti Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19684#99750 > I didn't get an error when I ran your query below using the left join, > but wouldn't the "WHERE Units.UnitID = #App_ClientID#" > need to be "WHERE Clients.ClientID = #App_ClientID#"  ? > or am I not understanding the join logic... > Opps... Yes....   Sorry... That's what you get with changing someone else's code and not being able to test it... ;o)
12/11/2002 01:56 PM
Author: Rick Faircloth Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19684#99625 This seems to work...what do you think? Instead of this: <CFQUERY Name="GetProperty" Datasource="#DSN#">      Select * from Properties where PropertyID                 in (Select PropertyID from Rentals where Rentals.ClientID                 in (Select ClientID from Clients where Clients.ClientID                     = #App_ClientID#)) </CFQUERY> I use this: <CFQUERY Name="GetProperty" Datasource="#DSN#">      Select p.*         from properties p, rentals r, clients c       where p.propertyid = r.propertyid          and r.clientid = c.clientid          and c.clientid = #App_ClientID# </CFQUERY> Rick <>< Thanks, Gordon...works good. How would I change this one to make it mySQL compatible? <CFQUERY Name="GetProperty" Datasource="#DSN#">      Select * from Properties where PropertyID                 in (Select PropertyID from Rentals where Rentals.ClientID                 in (Select ClientID from Clients where Clients.ClientID                     = #App_ClientID#)) </CFQUERY> Rick <>< SELECT  u.* FROM    Units u, Clients c WHERE   c.UnitID = u.UnitID AND             c.ClientID = #App_ClientID# At 11:49 AM 12/11/2002 -0500, you wrote: ----- Excess quoted text cut - see Original Post for more -----
12/11/2002 01:41 PM
Author: Rick Faircloth Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19684#99621 Thanks, Gordon...works good. How would I change this one to make it mySQL compatible? <CFQUERY Name="GetProperty" Datasource="#DSN#">      Select * from Properties where PropertyID                 in (Select PropertyID from Rentals where Rentals.ClientID                 in (Select ClientID from Clients where Clients.ClientID                     = #App_ClientID#)) </CFQUERY> Rick <>< SELECT  u.* FROM    Units u, Clients c WHERE   c.UnitID = u.UnitID AND             c.ClientID = #App_ClientID# At 11:49 AM 12/11/2002 -0500, you wrote: ----- Excess quoted text cut - see Original Post for more -----
12/11/2002 01:25 PM
Author: Cary Gordon Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19684#99611 SELECT  u.* FROM    Units u, Clients c WHERE   c.UnitID = u.UnitID AND             c.ClientID = #App_ClientID# At 11:49 AM 12/11/2002 -0500, you wrote: ----- Excess quoted text cut - see Original Post for more -----
12/11/2002 01:24 PM
Author: Rick Faircloth Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19684#99610 I didn't get an error when I ran your query below using the left join, but wouldn't the "WHERE Units.UnitID = #App_ClientID#" need to be "WHERE Clients.ClientID = #App_ClientID#"  ? or am I not understanding the join logic... Rick <>< > This query worked with MS Access, but not with MySQL... > > How should I change this to make it work? > Is it a subquery problem? > This is MySQL version 4.0.5 beta... > In mySQL you need to use Joins. <CFQUERY Name="GetUnit" Datasource="#DSN#">    Select *    FROM Units  LEFT JOIN Clients         ON Units.UnitID = Clients.UnitID    WHERE Units.UnitID = #App_ClientID# </CFQUERY> Regards Stephen
12/11/2002 01:22 PM
Author: Rick Faircloth Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19684#99608 Oh, brother.  I spent a quite a bit of time learning to handle subqueries and I've written an app that has some subqueries that are 7 subqueries deep...(probably not the best coding, but it worked...) Now, after your input and checking into the manual, I find that subqueries aren't supported in MySQL currently, but will be in 4.1...  :o( I'll work on the joins...thanks... Rick <>< > This query worked with MS Access, but not with MySQL... > > How should I change this to make it work? > Is it a subquery problem? > This is MySQL version 4.0.5 beta... > In mySQL you need to use Joins. <CFQUERY Name="GetUnit" Datasource="#DSN#">    Select *    FROM Units  LEFT JOIN Clients         ON Units.UnitID = Clients.UnitID    WHERE Units.UnitID = #App_ClientID# </CFQUERY> Regards Stephen
12/11/2002 01:05 PM
Author: Stephen Moretti Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19684#99599 > This query worked with MS Access, but not with MySQL... > > How should I change this to make it work? > Is it a subquery problem? > This is MySQL version 4.0.5 beta... > In mySQL you need to use Joins. <CFQUERY Name="GetUnit" Datasource="#DSN#">    Select *    FROM Units  LEFT JOIN Clients         ON Units.UnitID = Clients.UnitID    WHERE Units.UnitID = #App_ClientID# </CFQUERY> Regards Stephen
12/11/2002 01:00 PM
Author: Jochem van Dieten Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19684#99598 Rick Faircloth wrote: ----- Excess quoted text cut - see Original Post for more ----- Flatten the subquery to a join on UnitID. Jochem
12/11/2002 11:52 AM
Author: Rick Faircloth Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19684#99581 Hi, all. This query worked with MS Access, but not with MySQL... How should I change this to make it work? Is it a subquery problem? This is MySQL version 4.0.5 beta... <CFQUERY Name="GetUnit" Datasource="#DSN#">      Select * from Units U where U.UnitID                 in (Select UnitID from Clients where Clients.ClientID                     = #App_ClientID#) </CFQUERY> Thanks! Rick
<< Previous Thread Today's Threads Next Thread >>

Search cf-talk

May 24, 2012

<<   <   Today   >   >>
Su Mo Tu We Th Fr Sa
     1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31     

Designer, Developer and mobile workflow conference

Latest Fusion Authority Articles

Designer, Developer and mobile workflow conference

Want a number like 800-ColdFusion?

My Toll Free 800 Number provides a vanity phone number service to clients who want more than just digits. How would you like 800-coldfusion?

www.MyTollFree800Number.com