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
