db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Danny Gallagher <dannygallagh...@gmail.com>
Subject Select multiple rows as one
Date Tue, 05 Feb 2013 13:04:02 GMT
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,
   EXTENSION varchar(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

NEWFILE1 FILE1      mpg
NEWFILE2 FILE2      mpg
NEWFILE3 FILE3      mpg
NEWFILE4 FILE4      mpg
NEWFILE5 FILE5      mpg
NEWFILE6 FILE6      mpg

METADATA
NEWFILE1 ALTFILE1
NEWFILE1 ALTFILE2
NEWFILE1 ALTFILE3
NEWFILE1 ALTFILE4
NEWFILE2 ALTFILE4
NEWFILE2 ALTFILE6


select filedata.newname, metadata.altname from filedata, metadata
where filedata.newname = metadata.newname

Of course, this gives me
NEWFILE1 ALTFILE1
NEWFILE1 ALTFILE2
NEWFILE1 ALTFILE3
NEWFILE1 ALTFILE4

But I need:
NEWFILE1, ALTFILE1, ALTFILE2, ALTFILE3, ALTFILE4

Mime
View raw message