Doubt in Left join-urgent

Question

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

Answer 1

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

Answer 2

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 

Answer 3

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

Answer 4

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

Answer 5

LEFT JOIN examples:

http://www.sqlusa.com/bestpractices/leftjoin/

---

Kalman Toth SQL SERVER & BI TRAINING