Re: Oracle brain twister
| From: | Colin Pitts | Date: | Thu, 15 Jun 2000 20:05:48 +0000 |
| Subject: | Re: Oracle brain twister | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-440@lists.php.net to get a copy of this message | ||
In article <Pine.LNX.4.21.0006141811070.873-100000@thinkpad.php.net>,
rasmus@linuxcare.com (Rasmus Lerdorf) wrote:
> Hey, I am allowed to ask questions too.. ;)
>
> I am stuck on an SQL problem. I have the following representative table:
>
> package_id extract_tag extract_value
>
> 100 ARCH i386
> 100 NAME perl
> 101 ARCH i386
> 101 NAME php
>
>I need to perform order by and where clauses on the above data using the
>extract_tag as if the extract_tag was a column.
[snip]
> I need my view to look like this:
>
> package_id ARCH NAME
> 100 i386 perl
> 101 i386 php
I tried this out in MySQL, and came up with the following:
select t1.package_id, t1.extract_value as ARCH, t2.extract_value as NAME
from pkg_extract as t1, pkg_extract as t2 WHERE
(t1.package_id=t2.package_id) AND (t1.extract_tag='ARCH') AND
(t2.extract_tag='NAME');
+------------+------+------+
| package_id | ARCH | NAME |
+------------+------+------+
| 100 | i386 | perl |
| 101 | i386 | php |
+------------+------+------+
include('std_disclaimer.php');