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

re: Mysql Select query with php

On Sun, 16 Nov 2003 12:08:19 -0000, "Benjamin Dickgießer"
<Benjamin@dickgiesser.net> wrote:
[color=blue]
>Hi, I want to create a sql query but don't know if this is possible with
>mysql.[/color]

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).
[color=blue]
>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.[/color]

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)

re: Mysql Select query with php

4.1 does.


"Andy Hassall" <andy@andyh.co.uk> wrote in message
news:7ivervg03i679oscgssuqs37apng8n9m36@4ax.com...[color=blue]
> On Sun, 16 Nov 2003 12:08:19 -0000, "Benjamin Dickgießer"
> <Benjamin@dickgiesser.net> wrote:
>[color=green]
> >Hi, I want to create a sql query but don't know if this is possible with
> >mysql.[/color]
>
> Please don't multipost, you've sent an identical post to alt.php.sql. If[/color]
you[color=blue]
> want to post to multiple groups, then crosspost (one message, with[/color]
multiple[color=blue]
> groups in the Newsgroup header).
>[color=green]
> >The Query should do the following:
> >Select all db entries from table in which entry a is smaller than the[/color][/color]
number[color=blue][color=green]
> >(count) of all db entries with criteria x from another table.[/color]
>
> 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,[/color]
and[color=blue]
> then doing a second query against the first table to compare with entry[/color]
'a'.[color=blue]
> Assuming that your 'criteria x' is independent of the outer select,[/color]
though.[color=blue]
>
> --
> Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
> Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)[/color]

re: Mysql Select query with php

On Sun, 16 Nov 2003 16:15:01 GMT, "john" <mynameis@john.com> wrote:[color=blue]
>"Andy Hassall" <andy@andyh.co.uk> wrote in message
>news:7ivervg03i679oscgssuqs37apng8n9m36@4ax.com.. .[/color]
[color=blue][color=green]
>> But MySQL doesn't support subqueries.[/color]
>
>4.1 does.[/color]

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)