On 3/16/2005, "Hendra Susanto" <[EMAIL PROTECTED]> wrote:
>
>i have 2 table:
TableA
Name Birth
Sandra Jan 1,1980
Bob Feb 12,1972
TableB
Name Birth
Hendra Jan 4,1950
Joeli Feb 15,1962
How do i select the oldest 2 people from both table in php???
I've tried:
<?php
$statement = "(SELECT name FROM TableA)
UNION
(SELECT name FROM TableB)
LIMIT 2 ORDER BY Birth";
$result = mysql_query($statement);
while ($row = mysql_fetch_array($result)) echo $row[name];
?>
but it didn't work..
any other solution??
Each table contains about 50.000 records, and i have a perfect good reason
to keep them in a separate table.
>
>
>From http://dev.mysql.com/doc/mysql/en/union.html
"If you want to use an ORDER BY or LIMIT clause to sort or limit the
entire UNION result, parenthesize the individual SELECT statements and
place the ORDER BY or LIMIT after the last one. The following example
uses both clauses:
(SELECT a FROM tbl_name WHERE a=10 AND B=1)
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;
This kind of ORDER BY cannot use column references that include a table
name (that is, names in tbl_name.col_name format). Instead, provide a
column alias in the first SELECT statement and refer to the alias in the
ORDER BY, or else refer to the column in the ORDER BY using its column
position. (An alias is preferable because use of column positions is
deprecated.) "
Your example is close, but doesn't quite line up with the final
paragraph. Try:
(SELECT name, birth FROM TableA)
UNION
(SELECT name, birth FROM TableB)
ORDER BY Birth LIMIT 2;
I haven't had a chance to test this, but it *should* work.
Mike
Community email addresses:
Post message: [email protected]
Subscribe: [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
List owner: [EMAIL PROTECTED]
Shortcut URL to this page:
http://groups.yahoo.com/group/php-list
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php-list/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
