Results 1 to 4 of 4

Thread: Building a join query.

  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unhappy Building a join query.

    HI,

    Need help!..
    There are 2 tables, table1 and table2. Table1 has a primary key and it is a foreign key for table2. In table2, for the foreign key,there can be n number of rows or no rows at all.
    I need to establish a join between 2 tables based on the key and retrieve the rows irrespective of data present in table2. And if there are
    n rows in table2 for the key value, then the maximum value based on a particular column should be retrieved. If there is no row for that key in table2, then a null value should be fetched.

    Rows need to be fetched irrespective of data present in table2.[left outer join holds good, but since we need to retrieve the maximum value if there are rows in table2 and null if there are no rows, m stuck.]
    Please help.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932

  3. #3
    Join Date
    Apr 2011
    Posts
    2

    Hi

    Yes, i tried groupby and max() and it works. But , since i am retrieving 62 columns , i will ve to include include all the 61 columns(except max column) in groupby condition. Is there any other efficient way of implementing the same.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You can do group by in a inline view of second table and left outer join with firsrt table. You can put max on all other columns in second table.

    select ...
    from table 1
    left outer join (select max(), .... from table 2 group by ...) as a
    on t1.x=a.x

Posting Permissions

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