Click here to Skip to main content
15,881,413 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi All

I am using sql server database. I have two tables studentinfo and studentfees.

studentinfo has following values
===============================
studentid  studentname address    dob          isactive
    1         a        chennai    10.02.1988     no
    2         b        madurai    15.2.1987      no
    1         a        din        10.02.1988     yes
    3         c        trichy     16.2.1986      no
    2         b        madurai    15.2.1987      yes


studentfees has following values
=================================
studentid    class  fee     isactive
   1           I     2000    no
   2           II    4000    no
   1           I     458     yes
   3           III   545     no
   2           II    4000    yes


I want to get both table to be linked and the result will be look like following
<pre>

studentid studentname address dob class fee isactive

1 a din 10.02.1988 I 458 yes
3 c trichy 16.2.1986 III 545 no
2 b madurai 15.2.1987 II 400 yes

whenever the update the records both tables will be inserted and previous record will be inactive. I need a distinct values using studentid but it should not display duplicate values, only the last inserted records needed to display

Please help me

Thanks & Regards

Justin Diraviam.I
Posted
Updated 21-Jun-12 18:54pm
v2

1 solution

Hi Justin,

you have redundancy in your data. You have "fee" different for a single student id in your second table. So when you will use left join to fetch merged data, you will get duplicates. Because of there is no way to track which row you've inserted last, you go with two options: first remove the duplicate entries, second you should add a column to record timestamp for every entry & then you would be able to get the unique values without removing duplicates:

for the first option, I've created a sample query for you:

I've assumed your tables as s1 and s2 accordingly.

SQL
select s1.studentid, s1.studentname, s1.address, s1.dob, s2.class, s2.fee, s2.isactive

from s1 left join s2 on s1.studentid=s2.studentid;



Hope this helps!

Happy Coding :)
Sunny_K
 
Share this answer
 
v2
Comments
Justin Diraviam 22-Jun-12 1:20am    
Hi Sunny_K

Thanks for your response. I have already used the same query. I got duplicate values. Please Let me know how to add the timestamp and from this how to get get last records?
Sunny_Kumar_ 22-Jun-12 1:53am    
just add a column with type timestamp as - "alter table s2 add ts timestamp;" & pass "Current_Timestamp" as a value for this column at insert.
Justin Diraviam 22-Jun-12 8:57am    
Thank you for your response. If u dont mind can you give me the sample query for the above result using timestamp

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900