Join, with a twist

I have 3 tables, as follows:

Category (CategoryId, CategoryName)
DataSource (SourceId, CategoryId, CategoryValue)
Metadata (MetadataId, CategoryId, CategoryValue)

Since the Metadata for a dataset includes the categories relating to the source, I want to select all the CategoryNames and CategoryValues pertaining to a dataset and display them sorted on CategoryId. This means I have to extract the CategoryIds and CategoryValues from DataSource for a given SourceId and CategoryIds and CategoryValues from Metadata for a given MetadataId into 2 columns (not 4 !!) and join these with the Category table to extract the names.

Put simply, I want to append 2 columns from a table onto corresponding columns from another table.

Is this doable in straight SQL, or do I have to do it in Java ?

Thanks for any pointers.

Paul

Well, You can do this using SET operator, provided by the ORACLE. If your attributes defined in the SELECT clause have the same datatype in each table. In this example 'CategoryId' and 'CategoryValue' must have the same data type. Now your query should be look like:

SELECT a.CategoryId, a.CategoryName, b.CategoryValue
FROM Category, ( SELECT CategoryId, CategoryValue
FROM MateData
UNION /* Set Operator */
SELECT CategoryId, CategoryValue
FROM DataSource) b
where a.CategoryId = b.CategoryId
/

If I understand correctly your question then the above query should work.

Thanks, Shafique. That was just what I needed.

Cheers,
Paul