Mysql Select query with php

Hi, I want to create a sql query but don't know if this is possible with
mysql.
The Query should do the following:
Select all db entries from table in which entry a is smaller than the number
(count) of all db entries with criteria x from another table.
Thx for your help!
Benjamin Dickgießer

On Sun, 16 Nov 2003 12:08:19 -0000, "Benjamin Dickgießer"
<Benjamin@dickgiesser.net> wrote:

>Hi, I want to create a sql query but don't know if this is possible with
>mysql.

Please don't multipost, you've sent an identical post to alt.php.sql. If you
want to post to multiple groups, then crosspost (one message, with multiple
groups in the Newsgroup header).

>The Query should do the following:
>Select all db entries from table in which entry a is smaller than the number
>(count) of all db entries with criteria x from another table.

In most databases, you'd just use a subquery:

SELECT *
FROM table2
WHERE a < (SELECT COUNT(*)
FROM table1
WHERE 'criteria x')

But MySQL doesn't support subqueries.

So you're probably better off executing the subquery to get the count, and
then doing a second query against the first table to compare with entry 'a'.
Assuming that your 'criteria x' is independent of the outer select, though.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

4.1 does.


"Andy Hassall" <andy@andyh.co.uk> wrote in message
news:7ivervg03i679oscgssuqs37apng8n9m36@4ax.com...
> On Sun, 16 Nov 2003 12:08:19 -0000, "Benjamin Dickgießer"
> <Benjamin@dickgiesser.net> wrote:
>
> >Hi, I want to create a sql query but don't know if this is possible with
> >mysql.
>
> Please don't multipost, you've sent an identical post to alt.php.sql. If
you
> want to post to multiple groups, then crosspost (one message, with
multiple
> groups in the Newsgroup header).
>
> >The Query should do the following:
> >Select all db entries from table in which entry a is smaller than the
number
> >(count) of all db entries with criteria x from another table.
>
> In most databases, you'd just use a subquery:
>
> SELECT *
> FROM table2
> WHERE a < (SELECT COUNT(*)
> FROM table1
> WHERE 'criteria x')
>
> But MySQL doesn't support subqueries.
>
> So you're probably better off executing the subquery to get the count,
and
> then doing a second query against the first table to compare with entry
'a'.
> Assuming that your 'criteria x' is independent of the outer select,
though.
>
> --
> Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
> Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

On Sun, 16 Nov 2003 16:15:01 GMT, "john" <mynameis@john.com> wrote:
>"Andy Hassall" <andy@andyh.co.uk> wrote in message
>news:7ivervg03i679oscgssuqs37apng8n9m36@4ax.com.. .

>> But MySQL doesn't support subqueries.
>
>4.1 does.

4.1 is still alpha; when it's released, then it'll be fair to say MySQL
supports subqueries.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)