Results 1 to 4 of 4

Thread: query

  1. #1
    Join Date
    Aug 2009
    Posts
    1

    Unanswered: query

    Hi,

    I could use a little help with a db2 query.

    create table mainTable (
    id integer
    );

    create table table1 (
    id integer,
    start_date date
    );

    create table table1Child(
    table1Id integer,
    mainTableId integer
    );

    insert into mainTable values (1);
    insert into mainTable values (2);

    insert into table1 values (1, date('2009-01-02'));
    insert into table1 values (2, date('2009-01-03'));

    insert into table1Child values (1, 1);
    insert into table1Child values (1, 2);
    insert into table1Child values (2, 2);

    I would like to do a query such that the result is:

    mainTable.id, table1.start_date
    1, 2009-01-02
    2, 2009-01-03

    That is I get the records from the mainTable joined with table1 and I only get the records from mainTable that have the latest start_date. The mainTable.id must only appear once in the result.

    Any help would be appreciated.


    regards,
    Jannic

  2. #2
    Join Date
    Sep 2004
    Posts
    111
    Hope this may...

    db2 "select a.id, b.start_date from maintable a, table1 b where a.id=b.id"

    -U

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think the solution might incorporate the MAX() function and a GROUP BY...
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Jannic

    Your sample data was so simple that I couldn't understand your requirement.

    Because, I could make a query to get your sample result without seeing any table other than table1.
    Here is an example
    (This must not be your requered query!):
    Code:
    SELECT id         AS "mainTable.id"
         , start_date AS "table1.start_date"
      FROM table1
    ;
    My question is that all mainTableId in table1Child are in mainTable?
    If answer is yes, then you need not to see mainTable.
    So, I assumed that some mainTableId in table1Child may be not in mainTable.

    For example:

    mainTable:
    1d
    1
    3
    4

    table1:
    id start_date
    10 '2009-01-02'
    20 '2009-01-03'

    table1Child:
    table1Id mainTableId
    10 1
    10 2
    10 3
    10 4
    10 5
    20 2
    20 3

    Expected result:
    mainTable.id, table1.start_date
    4, 2009-01-02
    3, 2009-01-03

    Then, a query may be:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT (SELECT MAX(m.Id)
              FROM table1Child AS t1c
              JOIN mainTable   AS m
               ON  m.id = t1c.mainTableId
             WHERE t1c.table1Id = t1.id
           )          AS "mainTable.id"
         , start_date AS "table1.start_date"
      FROM table1 t1
    ;
    ------------------------------------------------------------------------------
    
    mainTable.id table1.start_date
    ------------ -----------------
               4 2009-01-02       
               3 2009-01-03       
    
      2 record(s) selected.
    If the data or result was defferent from your requirement,
    please give me enough sample data and expected result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •