Get Unique values from tables

Question

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 21-Jun-12 18:52pm

Justin Diraviam

Updated 21-Jun-12 18:54pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sunny_Kumar_ · Answer 1 · 2012-06-21T19:10:00

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