#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    2
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    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.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    2
    Rep Power
    0
    Thanks, Shafique. That was just what I needed.

    Cheers,
    Paul

IMN logo majestic logo threadwatch logo seochat tools logo