how to select selected number of records for each user from two tables

Question

0.00/5 (No votes)

See more:

i have two tables, 'users' and 'user_grading'. in 'users' tables i had user name and other details and 'userid' is the primary key. in 'user_grading' table i had grading of all users and the year of which the grading is, 'gradingid' is the primary key and 'userid' is the foreign key. now i need to get the last 3 year grading of each user with its name which is stored in other table. please help me how to do this in sql server 2005. the table details are as follows...

users:
userid int PK
User_Name text

user_grading:
gradingid int PK
userid int FK
grading text
year int

...........
and what i want is some what like this

UserName Year Grading
sam 2009 good
sam 2010 avg
sam 2011 good
john 2007 good
john 2009 avg
john 2010 good
... and so on
please tell me what to do

Posted 17-Aug-11 8:09am

MansoorKayani

Add a Solution

3 solutions

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)

Wendelius · Answer 1 · 2011-08-17T09:27:00

If I understood correctly, the last 3 gradings are not necessarily from last 3 years. In your example you have

john 2007 good
john 2009 avg
john 2010 good

So John doesn't have any grading for the year 2008.

If this is the problem, you can try something like:

SQL

SELECT *
FROM users u,
     user_grading ug
where u.userid = ug.userid
and   3 <= (select count(*)
            from user_grading ug2
            where ug2.userid = ug.userid
            and ug2.year > ug.year)

#realJSOP · Answer 2 · 2011-08-17T08:21:00

I'm not a sql expert by any means, but this might work...

SQL

select top 3 
       u.user_name
       ,g.year       
       ,g.name
from [users] u
inner join user_grading g 
on u.userid = g.userid
order by g.year desc

EDIT ==================

Okay then, try this:

SQL

select u.user_name
       ,g.year
       ,g.name
from [users] u
inner join user_grading g
on u.userid = g.userid
where g.year >= DATEPART(year, GETDATE()) - 2
order by u.user_name, g.year desc

Have you ever considered actually trying stuff yourself?

OriginalGriff · Answer 3 · 2011-08-17T08:31:00

Solution 2

Try:

SQL

SELECT u.User_name AS UserName, g.year AS Year, g.grading As Grading 
   FROM users AS u 
   JOIN user_grading AS g 
      ON u.userid=g.userid 
   WHERE g.Year>=DATEPART(year, GETDATE()) - 2

Posted 17-Aug-11 8:31am

OriginalGriff

how to select selected number of records for each user from two tables

3 solutions

Solution 4

Solution 1

Solution 2

Add your solution here

Preview 0