October 10th, 2003, 12:48 AM
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.
October 10th, 2003, 10:07 AM
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
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.
October 12th, 2003, 07:04 PM
Thanks, Shafique. That was just what I needed.