db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Select multiple rows as one
Date Tue, 05 Feb 2013 13:28:31 GMT
Hi Danny,

What you are trying to do sounds like a pivot table. You may want to 
read the following email thread on this topic: 
http://apache-database.10148.n7.nabble.com/Pivoting-tables-td101329.html 
One suggestion there is to use a user-defined aggregate to pivot the 
join. Unfortunately, user-defined aggregates are a new feature which 
won't appear until Derby 10.10 later this year.

Hope this helps,
-Rick

On 2/5/13 5:04 AM, Danny Gallagher wrote:
> Just can't seem to get this one right.
> Any help appreciated.
>
> I need a Query to return one row, from multiple rows of data
>
> Based on the following two tables and example data
>
> CREATE TABLE APP.FILEDATA
> (
>    NEWNAME varchar(50) PRIMARY KEY NOT NULL,
>    BASENAME varchar(50) NOT NULL,
>    EXTENSIONvarchar(10) NOT NULL
> );
>
> CREATE TABLE APP.METADATA
> (
>    NEWNAME varchar(50) NOT NULL,
>    ALTNAME varchar(50) NOT NULL,
>
>    FOREIGN KEY (NEWNAME)REFERENCES APP.FILEDATA(NEWNAME)
> );
>
> FILEDATA
>
> NEWFILE1FILE1      mpg
> NEWFILE2FILE2      mpg
> NEWFILE3FILE3      mpg
> NEWFILE4FILE4      mpg
> NEWFILE5FILE5      mpg
> NEWFILE6FILE6      mpg
>
> METADATA
> NEWFILE1ALTFILE1
> NEWFILE1ALTFILE2
> NEWFILE1ALTFILE3
> NEWFILE1ALTFILE4
> NEWFILE2ALTFILE4
> NEWFILE2ALTFILE6
>
>
> select filedata.newname, metadata.altname from filedata, metadata
> where filedata.newname = metadata.newname
>
> Of course, this gives me
> NEWFILE1ALTFILE1
> NEWFILE1ALTFILE2
> NEWFILE1ALTFILE3
> NEWFILE1ALTFILE4
>
> But I need:
> NEWFILE1, ALTFILE1, ALTFILE2, ALTFILE3, ALTFILE4
>
>
>
>


Mime
View raw message