Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    4

    Unanswered: is TOP 1 in JOIN possible

    Doing a query with two Tables normaly is done by A.IDA=B.IDA
    But also A.IDA>B.IDA is possible - but can give more than one join.

    I have the following query:

    SELECT * FROM TblA
    LEFT JOIN TblB ON TblB.Begin > TblA.End

    Now I want to get ONLY ONE joined record.

    Is there an syntax like:
    LEFT JOIN TOP 1 TblB ON TblB.Begin > TblA.End ???

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    SQL Server 2000:

    Select top N *
    from table
    order by column

    Oracle 9i:

    Select *
    from
    (select columns from table ORDER BY column)
    where rownum = 1;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    4
    Originally posted by r123456
    SQL Server 2000:

    Select top N *
    from table
    order by column

    Oracle 9i:

    Select *
    from
    (select columns from table ORDER BY column)
    where rownum = 1;
    Thank you. - But just selecting the top one of a table is not my problem.
    I need the top 1 in the JOIN statement, because I want to join one table with onother by joining from the second table only the ONE next elder record.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select *
    from
    tableB tb
    LEFT OUTER JOIN
    (select top N * from tableA where condition) v1 on
    v1.id = tb.id;

    This query will join all records of tableB with the first record of the set V1.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    4
    Originally posted by r123456
    ...
    (select top N * from tableA where condition) v1 on
    v1.id = tb.id;

    This query will join all records of tableB with the first record of the set V1.
    Sorry - but this doesn't help either
    because if top 1 selects a record with another v1.id than tb.id I get no joined records although there IS one (but not on top of the list v1)

    Or did I get something wrong...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select *
      
    from TblA 
    left outer
      join TblB 
        on TblB
    .Begin TblA.End
       
    and TblB.Begin
         
    = ( select max(Begin)
               
    from TblB 
              where Begin 
    TblA.End 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    4

    Thumbs up It works !

    Originally posted by r937
    PHP Code:
    select *
      
    from TblA 
    left outer
      join TblB 
        on TblB
    .Begin TblA.End
       
    and TblB.Begin
         
    = ( select max(Begin)
               
    from TblB 
              where Begin 
    TblA.End 

    THAT WORKS !!!!!!

    Thanks a lot !!!!!!!

Posting Permissions

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