select newname from filedata union select altname from metadata select 'NEWFILE1' union select altname from metadata where newname='NEWFILE1'
2013/2/5 Danny Gallagher <[email protected]> > 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 > > > > >
