RE: How to change this query from MS Access to MySQL Query

[Rick Faircloth]

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

-----Original Message-----
From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 11, 2002 1:47 PM
To: CF-Talk
Subject: RE: How to change this query from MS Access to MySQL Query


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



-----Original Message-----
From: Cary Gordon [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 11, 2002 1:22 PM
To: CF-Talk
Subject: Re: How to change this query from MS Access to MySQL Query


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:
>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
></CFQUERY>
>
>Thanks!
>
>Rick



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.