p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Complex MYSQL Query


Message #1 by "Trey Carroll" <treycarroll@y...> on Mon, 23 Sep 2002 17:48:23
I need to get a result set that drills down through several layers of 
DISTINCT.  I am having trouble seeing how to construct the query.  In 
Oracle SQL I would have constructed it like this: 

SELECT X FROM tablename WHERE (SELECT... ); 

But MySQL apparently doesn't let you do this.  How do you construct this 
type of query?  (This is for a php project.)
Message #2 by "Nikolai Devereaux" <yomama@u...> on Mon, 23 Sep 2002 12:19:51 -0700
The first (and probably easiest) thing to do is to switch to PostgreSQL --
pgsql offers transactions AND sub-selects.


If you're stuck with mysql, you can try using joins where possible, or break up
each query separately and merge/filter the results by hand.

Without more details about the queries or the db schema, I can't really say one
way or the other.


nik

Message #3 by Jason DeWitt <roxet@c...> on Mon, 23 Sep 2002 15:20:01 -0500
Just read this 
(http://slashdot.org/articles/02/09/23/1652243.shtml?tid=136) stoy on 
slashdot, it has a link to a press release from mySQL about it 
supporting transactions either now or in the near future.

Nikolai Devereaux wrote:

>The first (and probably easiest) thing to do is to switch to PostgreSQL --
>pgsql offers transactions AND sub-selects.
>
>
>If you're stuck with mysql, you can try using joins where possible, or break up
>each query separately and merge/filter the results by hand.
>
>Without more details about the queries or the db schema, I can't really say one
>way or the other.
>
>
>nik
>
>
>
>  
>



Message #4 by "Nikolai Devereaux" <yomama@u...> on Mon, 23 Sep 2002 13:34:37 -0700
Thanks for the link, will check it out in a bit.

MySQL offers some transaction support NOW, with their INNODB table type, but
the performance penalty for enabling transactions is obscene -- queries
typically run 2-4 times slower than normal.

Plus, that still doesn't help us in the cases where we need nested queries
(sub-selects).


take care,

nik

Message #5 by "jorge" <jorge@d...> on Tue, 24 Sep 2002 08:07:14 -0500
Nik i have seen that there is transaction support to myslq though a berkley
module i just have read that is ti true?
has any body use it?

Jorge
----- Original Message -----
From: "Nikolai Devereaux" <yomama@u...>
To: "beginning php" <beginning_php@p...>
Sent: Monday, September 23, 2002 2:19 PM
Subject: [beginning_php] RE: Complex MYSQL Query


>
> The first (and probably easiest) thing to do is to switch to PostgreSQL --
> pgsql offers transactions AND sub-selects.
>
>
> If you're stuck with mysql, you can try using joins where possible, or
break up
> each query separately and merge/filter the results by hand.
>
> Without more details about the queries or the db schema, I can't really
say one
> way or the other.
>
>
> nik
>
>



  Return to Index