[thelist] MySQL sub-select with PHP [was: sql cross table exclusion??]

Michael Mell mike at nthwave.net
Fri Aug 30 19:26:01 CDT 2002


Hi Rudy,
I can't say enough how appreciative I am that you offer so much to the list so
freely.
Thank you.

I had to read this over a couple of times
> it's a good thing you specified "users who don't have X or Y" because if
> you had said "users who have neither X nor Y" the solution is totally
> different!!
before I realized that I do in fact need to know "users who have neither X nor
Y".

Why are they completely different? I don't understand.



Instead of relying on you to provide me with another genius answer, and to
better address my real-world problem (having nothing to do with cars), I think
I'm going to tackle this problem in another, revolutionary way.

So, we all know that MySQL does not have sub-select capability.
(The bottom of http://www.mysql.com/products/mysql-4.0/index.html says that
MySQL 4.1 WILL have "nested subqueries." Yea!)

In order to solve my problem without waiting for MySQL 4.1, it seems I need to
implement a strategy in PHP that allows iterative sub-selects.

The whole purpose of my query is to find a set of users that satisfy an
arbitrary set of conditions. In the car example, one set of arbitrary
conditions was "owns neither Ford nor Porsche." There are many other possible
criteria including zip code, age, etc.

I'm just looking for tips and suggestions from the list on the following
scenario. Here's how it will work:
~ use the search form to select a group of users.
~ initiate a PHP session
~ store the list of users found in the query in a session variable.
~ return the client browser to the search page, and display the results of the
search
~ subsequent searches will have the option to "search only the current set of
users"
~ if the "search current set" option is ON, then I will pull the list of
userID's from the session variable and append " where userID in
($sessionVariableUserIDList)".

This effectively implements an "iterative sub-select."

The only problem with this scenario is that there might be 10,000 or even
50,000 userID's in the first and subsequent searches. I don't know how MySQL
will perform with a list of 50,000 userID's in a "WHERE ... IN..." clause.

Here's where I'd like some genius: what other options do I have for
implementing this sort of iterative sub-select? Is what I've outlined the only
possible method?

Many thanks to all the heavy thinkers out there and to all those who follow
along for the fun of it.
:-)
mike



rudy wrote:

> > Is there a clean way to do this in sql (MySQL)?
>
> hi michael
>
> that's actually two questions   ;o)
>
> you were right, it is a classic sql problem
>
> but mysql does not allow subqueries, so you cannot use the classical
> solution
>
> > How can I select all users who do not own a Ford or a Porsche?
>
>    select userid, name
>      from user xx
>     where not exists
>           ( select 1
>               from usercar
>             inner
>               join car
>                 on usercar.carID = car.carID
>              where usercar.userID = xx.userID
>                and cartype in ('Ford','Porsche')
>           )
>
> here's how it works in english --
>
> look at all the cars owned by each user and check each one to see if it's a
> 'Ford' or 'Porsche'
>
> if it is one of these, then this isn't a user you want
>
> the result is all the users who aren't users you didn't want
>
> ;o)
>
> or, in pseudo-sql --
>
> select the users who don't have a Ford or Porsche
>
> (astute readers will note that, for rudy, the pseudo-sql is a lot closer to
> both the original statement of the problem and the actual sql than the
> attempted english explanation of the correlated subselect)
>
> it's a good thing you specified "users who don't have X or Y" because if
> you had said "users who have neither X nor Y" the solution is totally
> different!!
>
> here's another way to do the "don't have X or Y" query, not as efficient as
> the "not exists" approach
>
> if you test this, please do be sure and let me know if it works --
>
>    select user.userID, name
>      from user
>    left outer
>      join usercar
>        on user.userID = usercar.userID
>    left outer
>      join car
>        on usercar.carID = car.carID
>       and cartype in ('Ford','Porsche')
>    group
>        by user.userID, name
>    having count(cartype) = 0
>
> rudy
> http://rudy.ca/
>
> --
> For unsubscribe and other options, including
> the Tip Harvester and archive of thelist go to:
> http://lists.evolt.org Workers of the Web, evolt !

--

mike[at]nthwave.net





More information about the thelist mailing list