How to change this query from MS Access to MySQL Query

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

Rick Faircloth wrote: ----- Excess quoted text cut - see Original Post for more ----- Flatten the subquery to a join on UnitID. Jochem

> 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

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

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

> 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)

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 -----

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 -----

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 -----

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

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