Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Question Unanswered: Select ALL from table, ONE from related table.

    I have two tables

    [table1] is a master table.
    [table2] contains version information.

    There are many [table2] entities for every one [table1] entity.

    I want to join the tables in a query, returning all the entities in [table1] and the latest [version] of [table2].

    I can do this programmatically but would prefer to let the DB do the work.

    So far:

    SELECT
    table1.*,
    table2.*
    FROM
    table1 INNER JOIN table2
    ON (table1.id = table2.table1_id)
    ORDER BY
    table1.id,
    table2.version

    I know that you can restrict the join using a TOP function or similar in Oracle - how do you do it in SQL Server?

    Hope you can help,

    Andrew

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try this idea (uoy can change version to max id or something else):

    SELECT
    table1.*,
    table2.*
    FROM
    table1 INNER JOIN table2
    ON (table1.id = table2.table1_id)
    where table2.version=(select max(version) from table2 where table1.id = table2.table1_id)
    ORDER BY
    table1.id,
    table2.version

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    Nice one, worked a treat. Thanks mate.

  4. #4
    Join Date
    Oct 2003
    Posts
    2

    same thing but a little different

    Hello need to do almost the exact same thing but slightly different.

    In his example he uses a version, and needs only the latest version returned, I would need the last 2 versions returned if that makes sense.

    Code is same as other example.

    Thanks

Posting Permissions

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