SQL Join

[germanshorthairpointer]

Hello,

I'm trying to do a join based on the following tables:

Person(person_id,person_name)
Grade(grade_id,grade_person_id,grade_score)

The data looks like this:

Person:
1,John
2,Dave

Grade:
1,1,80
2,1,90
3,2,60
4,2,70

I'd like a query that returns the each persons name along with their
highest grade.

What would the query be?

Thanks!

[David Portas]

ge********************@gmail.com wrote:

Hello,

I'm trying to do a join based on the following tables:

Person(person_id,person_name)
Grade(grade_id,grade_person_id,grade_score)

The data looks like this:

Person:
1,John
2,Dave

Grade:
1,1,80
2,1,90
3,2,60
4,2,70

I'd like a query that returns the each persons name along with their
highest grade.

What would the query be?

Thanks!

SELECT g.grade_person_id, p.person_name, MAX(g.grade_score) AS
grade_score
FROM person AS p
JOIN grade AS g
ON p.person_id = g.grade_person_id
GROUP BY p.person_name, g.grade_person_id ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[--CELKO--]

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. 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 had followed minimal Netiquette, would you have posted something
that shows that you are dealing with sets (collective or plural names)
that have particular roles in the data model:

CREATE TABLE Students -- we doi not grade people unless we are
cannibals
(student_id INTEGER NOT NULL PRIMARY KEY,
student_name VARCHAR(35) NOT NULL)';

CREATE TABLE Grades -- in what?
(student_id INTEGER NOT NULL
REFERENCES Students (student_id)
grade_score DECIMAL (5,2) NOT NULL,
PRIMARY KEY (student_id, grade_score);

The data looks like this: .. <<

I HOPE NOT!! That would a disaster.
I'd like a query that returns the each person's [student] name along with their

highest grade. <<

Why does this smell of homework assignment. So first thing is to
answer your quesiton then track you down and report you for cheating
to your school if needed.

SELECT student_name, MAX(grade_score)
FROM Grades AS G, Students AS S
WHERE S.student_id = G.student_id ;

[--CELKO--]

The posting was so bad I got in ahury and left off the GROUP BY:
SELECT S.student_id, S.student_name, MAX(G.grade_score)
FROM Grades AS G, Students AS S
WHERE S.student_id = G.student_id
GROUP BY S.student_id, S.student_name;

You have to have the student_id in case of duplicate names.

[germanshorthairpointer]

Thanks David!

I've created a view, so my Perl connectors can easily script data
into a meta directory. From the meta directory, data is scripted into
two large directories OpenLDAP and AD. My initial question was
rudimentary, because my goal was to understand how a join would work
the way I needed it to.

Now, to be more specific. I have another table from which I need to
add two more fields to my view. Let's say I have a table called
FACULTY. That table has YR_CDE, TRM_CDE, ID_NUM, and JOB_TIME. ID_NUM
is the primary key and JOB_TIME is datetime. I need to add to my join
YR_CDE and TRM_CDE from FACULTY for each ID_NUM. The latest JOB_TIME
should determine which YR_CDE and TRM_CDE is joined.

Let me know if you need more info. I don't work with MS SQL
everyday, so forgive me if my question is not clear enough.

Grant

CREATE VIEW dbo.META_VIEW
AS
SELECT TOP 100 PERCENT
dbo.NAME_MASTER.ID_NUM AS person_id,
dbo.NAME_MASTER.LAST_NAME AS person_lname,
dbo.NAME_MASTER.FIRST_NAME AS person_fname,
dbo.NAME_MASTER.EMAIL_ADDRESS AS person_email,
dbo.ADDRESS_MASTER.ADDR_LINE_1 AS person_street,
dbo.ADDRESS_MASTER.ADDR_LINE_2 AS person_street2,
dbo.ADDRESS_MASTER.ADDR_LINE_3 AS person_street3,
dbo.ADDRESS_MASTER.CITY AS person_city,
dbo.ADDRESS_MASTER.STATE AS person_state,
dbo.ADDRESS_MASTER.ZIP AS person_zip,
dbo.ADDRESS_MASTER.COUNTRY AS person_country,
dbo.ADDRESS_MASTER.PHONE AS person_phone,
dbo.BIOGRAPH_MASTER.SSN AS person_ssn,
dbo.BIOGRAPH_MASTER.BIRTH_DTE AS person_dob,
dbo.STUDENT_MASTER.CURRENT_CLASS_CDE AS person_stu_year,
dbo.STUDENT_MASTER.CUR_STUD_DIV AS person_stu_div,
dbo.STUDENT_MASTER.MOST_RECNT_YR_ENR AS person_stu_enrolled,
dbo.STUDENT_MASTER.MOST_RECNT_TRM_ENR AS
person_stu_lastterm, dbo.TW_WEB_SECURITY.ACCESS_CDE AS person_password,
dbo.STUDENT_MASTER.TRM_HRS_ENROLLED AS person_stu_hours,
dbo.STUDENT_MASTER.TUITION_CDE AS person_stu_tuition,
dbo.ROOM_ASSIGN.ROOM_CDE AS person_stu_room,
dbo.ROOM_ASSIGN.BLDG_CDE AS person_stu_building,
dbo.NAME_MASTER.PREFERRED_NAME AS person_pname,
dbo.NAME_MASTER.MIDDLE_NAME AS person_mname
FROM
dbo.NAME_MASTER LEFT OUTER JOIN
dbo.STUDENT_MASTER ON dbo.NAME_MASTER.ID_NUM =
dbo.STUDENT_MASTER.ID_NUM
LEFT OUTER JOIN
dbo.ADDRESS_MASTER ON dbo.NAME_MASTER.ID_NUM =
dbo.ADDRESS_MASTER.ID_NUM
AND
dbo.NAME_MASTER.CURRENT_ADDRESS = dbo.ADDRESS_MASTER.ADDR_CDE
LEFT OUTER JOIN
dbo.TW_WEB_SECURITY ON dbo.NAME_MASTER.ID_NUM =
dbo.TW_WEB_SECURITY.ID_NUM
LEFT OUTER JOIN
dbo.ROOM_ASSIGN ON dbo.STUDENT_MASTER.ID_NUM = dbo.ROOM_ASSIGN.ID_NUM
AND
dbo.ROOM_ASSIGN.SESS_CDE = '2006-FA'
LEFT OUTER JOIN
dbo.BIOGRAPH_MASTER ON dbo.NAME_MASTER.ID_NUM =
dbo.BIOGRAPH_MASTER.ID_NUM
WHERE
(dbo.NAME_MASTER.ID_NUM <> '') AND (dbo.NAME_MASTER.NAME_FORMAT IS
NULL) OR (dbo.NAME_MASTER.NAME_FORMAT <> 'B')

[Erland Sommarskog]

ge********************@gmail.com (ge********************@gmail.com) writes:

I've created a view, so my Perl connectors can easily script data
into a meta directory. From the meta directory, data is scripted into
two large directories OpenLDAP and AD. My initial question was
rudimentary, because my goal was to understand how a join would work
the way I needed it to.

Now, to be more specific. I have another table from which I need to
add two more fields to my view. Let's say I have a table called
FACULTY. That table has YR_CDE, TRM_CDE, ID_NUM, and JOB_TIME. ID_NUM
is the primary key and JOB_TIME is datetime. I need to add to my join
YR_CDE and TRM_CDE from FACULTY for each ID_NUM. The latest JOB_TIME
should determine which YR_CDE and TRM_CDE is joined.
Joined to what?

I'm afraid that I don't understand. If ID_NUM is a primary key how could
then JOB_TIME then determine which YR_CDE and TRM_CDE to join? Is it the
case that the primary key is (ID_NUM, JOB_TIME)?

Making this wild assumption, the join would look something like:

JOIN FACULTY F ON ?.ID_NUM = F.ID_NUM
JOIN (SELECT ID_NUM, maxtime = MAX(JOB_TIME)
FROM FACULTY
GROUP BY ID_NUM) AS FMAX ON F.ID_NUM = FMAX.ID_NUM
AND F.JOB_TIME = FMAX.maxtime

Since I don't know how FACULTY is related to the other tables, I
can't make it any better than this.
CREATE VIEW dbo.META_VIEW
AS
SELECT TOP 100 PERCENT

Remove the TOP 100 PERCENT. It serves no purpose.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[germanshorthairpointer]

Thanks Erland!

Joined to what?
I would like YR_CDE and TRM_CDE added to my view (if a respective
ID_NUM is in the FACULTY table)
I'm afraid that I don't understand. If ID_NUM is a primary key how could
then JOB_TIME then determine which YR_CDE and TRM_CDE to join? Is it the
case that the primary key is (ID_NUM, JOB_TIME)?

ID_NUM is the primary key in the FACULTY table. However, each ID_NUM
may have many different YR_CDE and TRM_CDE in the FACULTY table. The
row with the latest JOB_TIME for a respective ID_NUM contains the
latest Year (YR_CDE) and Term code (TRM_CDE) that a faculty member has
taught. Does that help any? Thanks for your help!!!

[Erland Sommarskog]

ge********************@gmail.com (ge********************@gmail.com) writes:

Joined to what?
I would like YR_CDE and TRM_CDE added to my view (if a respective
ID_NUM is in the FACULTY table)

Unfortunately that tells me very little. When you join is an operation
you perform between two tables. All I know is that you have a table
FACULTY, but I don't know how it relates to the other tables you have.
ID_NUM is the primary key in the FACULTY table. However, each ID_NUM
may have many different YR_CDE and TRM_CDE in the FACULTY table. The
row with the latest JOB_TIME for a respective ID_NUM contains the
latest Year (YR_CDE) and Term code (TRM_CDE) that a faculty member has
taught. Does that help any? Thanks for your help!!!

I'm sorry, but this is contracdictory. First you say that ID_NUM is the
primary key of FACULTY. This means that this is a unique value. That
is there is at most one row for a certain value of ID_NUM. Because that
is what "primary key" means: one more columns that togather uniquely
identifies a row.

Next you say that "row with the latest JOB_TIME for a respective ID_NUM".
This indicates that more than one row for the same ID_NUM. In such
ID_NUM cannot be a primary key on its on.

But (ID_NUM, JOB_TIME) could be the composite key. In such case the
fragment in my previous post should give you what you want:

JOIN FACULTY F ON ?.ID_NUM = F.ID_NUM
JOIN (SELECT ID_NUM, maxtime = MAX(JOB_TIME)
FROM FACULTY
GROUP BY ID_NUM) AS FMAX ON F.ID_NUM = FMAX.ID_NUM
AND F.JOB_TIME = FMAX.maxtime

You would have to replace the question mark with table in the
view that FACULTY is related to.

If this does not work out, I suggest that you post the CREATE TABLE
statements for your tables - including definitions of primary keys
and foreign keys.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx