none
Doubt in Left join-urgent

    Pertanyaan

  • there are two tables..
    using left join,

    i have to fetch the last record of the table in the right(right side table).. 
    at the same time ,the condition is that there are five records that match the left table,


    for eg,
    table 1(left) - has an ID 1
    and table 2(right)  - has same ID existing in 5 rows.. but i have to fetch only the last matching record from the table 2.
    Also table 2 has unique column date.

    Kindly help me.............

    by johnny

    07 Maret 2012 3:50

Jawaban

  • I think your requirement is similar like this:

    sql query - join that return only last row

    SELECT  NOM,PRENOM,OEDP.NUM_EMP,N_A_S,SIT_STATUT,PERMIS,DATE_EMBAUCHE,ADRESSE1,VILLE1,PROVINCE1,CODE_POSTAL1,TEL_RESIDENCE
    FROM    ODS_EMPLOYE_DOSSIER_PERSONNEL AS OEDP
    CROSS APPLY 
            (
            SELECT  TOP 1 *
            FROM    ODS_SITUATION_POSTE AS OSP
            WHERE   OEDP.NUM_EMP = OSP.NUM_EMP
            ORDER BY
                    SIT_DATE_CHG DESC
            ) OSP
    ORDER BY
            OEDP.NUM_EMP


    Regards, Nighting Liu

    07 Maret 2012 4:05
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. This is minimal polite behavior on a SQL forum. 

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

    What you did post makes no sense. I will guess at it and hope you read any basic SQL book before you code again. 

    >> there are two tables using LEFT [OUTER] JOIN,<<

    Great. What are the names? What is the join condition? 

    >> I have to fetch [sic] the last [sic] record [sic] of the table in the right(right side table) at the same time ,the condition is that there are five records [sic] that match the left table, <<

    FETCH is the action of cursor returning a single row; did you mean SELECRT? Rows are nothing like a record; files have records. The correct terms are “preserved” and “unpreserved” tables when talking about outer joins. While not required, good SQL programmers put the optional key word OUTER in their code as documentation. Is this left table the preserved one? 

    >> table 1(left) - has an "id" [sic] of 1 <<

    There is no such thing as a universal, magical “id”; it has to be a “<something in particular>_id” in a valid data model. See what a pain missing DDL is and why we ask for that minimal Netiquette? 

    >> and table 2(right) - has same "id" [sic] existing in 5 rows.. but I have to fetch only the last matching record [sic] from the table 2. Also table 2 has unique column date. <<

    Is that magical “id” a FOREIGN KEY? Is vague “something_date” the ordering in the npreserved table? Let me replace your personal narrative with some SQL skeletons:

    CREATE TABLE Students
    (student_id INTEGER NOT NULL PRIMARY KEY,
     ..);

    CREATE TABLE Exams
    (student_id INTEGER NOT NULL 
      REFERENCES Students(student_id), 
     exam_date DATE NOT NULL,
     exam_score INTEGER NOT NULL,
     PRIMARY KEY (student_id, exam_date),
     ..);

    This is a little fancy but compact

    SELECT X1.student_id, X2.exam_score, X2.exam_date
      FROM (SELECT Student_id FROM Students) AS X1
           LEFT OUTER JOIN 
           (SELECT X.student_id, x.exam_score, X.exam_date
              FROM (SELECT student_id, exam_score, exam_date,
                           MAX(exam_date) 
                           OVER (PARTITION BY student_id)
                            AS last_exam_date
                      FROM Exams) AS X
             WHERE X.exam_date = X.last_exam_date
             AS X2
          ON X1.student_id = X2.student_id;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    07 Maret 2012 4:56
  • Hi John,

    If you are only interested in rows that match at least five rows in
    the other table, why use an outer join at all? An inner join is more
    efficient, and the only extra an outer join gives you is that it
    returns rows that are not matched.

    Also, SQL Server tables have no implied order, so the term "last
    record" without specifying a sort order is meaningless.

    Here is a first stab, but probably wrong. If you post CREATE TABLE
    statements, INSERT statements with illustrative sample data, and expected results, I can give it another try.

    SELECT MAX(something)
    FROM     table1 AS a
    WHERE
     (SELECT COUNT(*)
        FROM     table2 AS b
        WHERE    b.ID = a.ID) >= 5;

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    07 Maret 2012 8:23

Semua Balasan

  • I think your requirement is similar like this:

    sql query - join that return only last row

    SELECT  NOM,PRENOM,OEDP.NUM_EMP,N_A_S,SIT_STATUT,PERMIS,DATE_EMBAUCHE,ADRESSE1,VILLE1,PROVINCE1,CODE_POSTAL1,TEL_RESIDENCE
    FROM    ODS_EMPLOYE_DOSSIER_PERSONNEL AS OEDP
    CROSS APPLY 
            (
            SELECT  TOP 1 *
            FROM    ODS_SITUATION_POSTE AS OSP
            WHERE   OEDP.NUM_EMP = OSP.NUM_EMP
            ORDER BY
                    SIT_DATE_CHG DESC
            ) OSP
    ORDER BY
            OEDP.NUM_EMP


    Regards, Nighting Liu

    07 Maret 2012 4:05
  • with temp as
    (
    select id,date,ROW_NUMBER() over (order by date) as rn 
    from table2 
    left join table1 on table1.id = table2.id
    )
    select * from temp where rn=5

    Sri 
    07 Maret 2012 4:11
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. This is minimal polite behavior on a SQL forum. 

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

    What you did post makes no sense. I will guess at it and hope you read any basic SQL book before you code again. 

    >> there are two tables using LEFT [OUTER] JOIN,<<

    Great. What are the names? What is the join condition? 

    >> I have to fetch [sic] the last [sic] record [sic] of the table in the right(right side table) at the same time ,the condition is that there are five records [sic] that match the left table, <<

    FETCH is the action of cursor returning a single row; did you mean SELECRT? Rows are nothing like a record; files have records. The correct terms are “preserved” and “unpreserved” tables when talking about outer joins. While not required, good SQL programmers put the optional key word OUTER in their code as documentation. Is this left table the preserved one? 

    >> table 1(left) - has an "id" [sic] of 1 <<

    There is no such thing as a universal, magical “id”; it has to be a “<something in particular>_id” in a valid data model. See what a pain missing DDL is and why we ask for that minimal Netiquette? 

    >> and table 2(right) - has same "id" [sic] existing in 5 rows.. but I have to fetch only the last matching record [sic] from the table 2. Also table 2 has unique column date. <<

    Is that magical “id” a FOREIGN KEY? Is vague “something_date” the ordering in the npreserved table? Let me replace your personal narrative with some SQL skeletons:

    CREATE TABLE Students
    (student_id INTEGER NOT NULL PRIMARY KEY,
     ..);

    CREATE TABLE Exams
    (student_id INTEGER NOT NULL 
      REFERENCES Students(student_id), 
     exam_date DATE NOT NULL,
     exam_score INTEGER NOT NULL,
     PRIMARY KEY (student_id, exam_date),
     ..);

    This is a little fancy but compact

    SELECT X1.student_id, X2.exam_score, X2.exam_date
      FROM (SELECT Student_id FROM Students) AS X1
           LEFT OUTER JOIN 
           (SELECT X.student_id, x.exam_score, X.exam_date
              FROM (SELECT student_id, exam_score, exam_date,
                           MAX(exam_date) 
                           OVER (PARTITION BY student_id)
                            AS last_exam_date
                      FROM Exams) AS X
             WHERE X.exam_date = X.last_exam_date
             AS X2
          ON X1.student_id = X2.student_id;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    07 Maret 2012 4:56
  • Hi John,

    If you are only interested in rows that match at least five rows in
    the other table, why use an outer join at all? An inner join is more
    efficient, and the only extra an outer join gives you is that it
    returns rows that are not matched.

    Also, SQL Server tables have no implied order, so the term "last
    record" without specifying a sort order is meaningless.

    Here is a first stab, but probably wrong. If you post CREATE TABLE
    statements, INSERT statements with illustrative sample data, and expected results, I can give it another try.

    SELECT MAX(something)
    FROM     table1 AS a
    WHERE
     (SELECT COUNT(*)
        FROM     table2 AS b
        WHERE    b.ID = a.ID) >= 5;

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    07 Maret 2012 8:23