db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jean-Yves Linet <jy.li...@gmail.com>
Subject Re: Select multiple rows as one
Date Tue, 05 Feb 2013 13:22:48 GMT
select newname from filedata
union
select altname from metadata

select 'NEWFILE1'
union
select altname from metadata where newname='NEWFILE1'


2013/2/5 Danny Gallagher <dannygallagher2@gmail.com>

> 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