Help needed with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help needed with query

Author

Topic

MichaelG
Starting Member

11 Posts

Posted - 2003-03-25 : 12:21:14

I have two tables which hold pupil data.

Table 1 has columns for GroupType, GroupName and GroupID. eg.
Class 3A, 23
Class 3B 24
House West 46
House East 47

Table 2 has columns for StudentID (which references a Students table) and GroupID from Table1.
Jones 24
Jones 46

So Jones is in Class '3B' and House 'West'

I need a query that can return StudentID and Class for all
Students in West.

Can this be done?

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-25 : 13:08:34

Can you chang theway the data is stored so you can normalize it? Why have 2 id's that represent a student. That's like creating a new id everytime you want to add a new attribute to a student. I'm sure a student can have many classes, but isn't it so that a particular class (for a given date and time) can only happen in 1 place? (I'm assuming West is a building).

Brett

8-)

MichaelG
Starting Member

11 Posts

Posted - 2003-03-25 : 13:51:21

Hi Brett,

There is only 1 StudentID generated when a student is added to the StudentRegistration table. The idea is that the student can belong to an abitrary number of groups or associations. These groups are declared in Table1, eg. a Class group subdivided into Forms (3A, 3B, etc), a House group subdivided (East, West, North, etc). Other groups can be declared as required, eg. Games, Musical Instruments, Drama club, whatever.

Table2 matches the Students to the groups and has a row (StudentID, GroupID) for each group the Student belongs to.

I don't know of another way of assigning students to an unknown number of groups.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-25 : 14:27:31

Well, ok...you've got an "artificial" relationship between House and Class, and that's through student. Normalized data would have this information in separate tables.

Guys, what's this: 1st Normal?

Anyway, lets see....break it up in to pieces. First get all West rows:

Select GroupId From Table1 Where GroupName = 'West' and GroupType = 'House'

Then get all of the student Rows for West:

Select * from Table2 o Where Exists (
Select 1 From Table1 i Where GroupName = 'West' and GroupType = 'House'
And o.Group_Id = i.Group_Id)

Then find where those Students are in table1 for their class

Select * from table1 Where Group_Type = 'Class' and GroupId In (Select GroupId from Table2 o Where Exists (
Select 1 From Table1 i Where GroupName = 'West' and GroupType = 'House'
And o.Group_Id = i.Group_Id)

owww..just got a brain cramp...really, unless you can't, you should really normalize your data...

Good Luck

ps check out: http://www.sqlteam.com/item.asp?ItemID=122

Brett

8-)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-25 : 14:29:29

psuedo-code below ...

create table students ( name varchar(5) not null primary key )

create table classes (name varchar(5) not null primary key )

create table houses (name varchar(5) not null primary key )

create table classassignments (
student varchar(5) not null refereneces students(name),
class varchar(5) not null references classes(name),
constraint pk_ca primary key (student,class) )

create table houseassignments (
student varchar(5) not null references students(name),
house varchar(5) not null references houses(name),
constratint pk_ha primary key (student,house) )

Jay White
{0}

MichaelG
Starting Member

11 Posts

Posted - 2003-03-25 : 16:01:19

Hi Brett,

I'm still trying to get my head round this but so far no success.
It's 9.00pm here now so I'm going to try again with a fresh brain in the morning.

I'm willing to reorganise the data but the problem is that these groups are user definable and unknown at design time so I can't create tables for them. Class and House are just two 'Starter' groups. The user can create others and then add students to any or all of them.

Thanks for your help.
Michael

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-25 : 16:54:25

Well it's like Jay said.

Why is the design so dynamic? If the structure is that way, (and has to remain that way), I might create views.

One question I do have though, is if they can add any group at any time, how will you know to write a query that would use that data.

That aside, the following will create a "virtual" relational database. Just run it nightly (makes sure to use the same code to perform the drops, or use some manner not to recreate ones that exist).

Once built you can use the views in sql joins.

Good Luck. Hope this helps:

CREATE TABLE Table1 (GroupType varchar(10), GroupName varchar(10), GroupID int)
Go

INSERT INTO Table1 (GroupType, GroupName, GroupID)
Select 'Class', '3A', 23 Union All
Select 'Class', '3B', 24 Union All
Select 'House', 'West', 46 Union All
Select 'House', 'East', 47
Go

CREATE TABLE Table2 (StudentID varchar(25), GroupID Int)
Go

INSERT INTO Table2 (StudentId, GroupID)
SELECT 'Jones', 24 Union All
SELECT 'Jones', 46
Go

Declare @SQL varchar(4000), @GroupType varchar(10)

DECLARE View_cursor CURSOR FOR
SELECT DISTINCT GroupType
FROM Table1

OPEN View_cursor

FETCH NEXT FROM View_cursor
INTO @GroupType

WHILE @@FETCH_STATUS = 0
BEGIN

Select @SQL = 'CREATE VIEW '+RTRIM(@GroupType)+' AS SELECT GroupName, GroupID FROM Table1 WHERE GroupType = '
+ ''''+@GroupType+''''
Select @SQL
Exec(@SQL)

FETCH NEXT FROM View_cursor
INTO @GroupType

END

CLOSE View_cursor
DEALLOCATE View_cursor

SELECT * FROM House
Go

SELECT * FROM Class
Go

DROP TABLE table1

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-25 : 17:22:01

This is a pretty easy one ... you are just grouping students into Groups ....

Create table Groups (GroupID int not null primary key, GroupName varchar(10))

create table Students (StudentID int not null primary key, Studentname varchar(30))

create table StudentsGroups(
GroupID int not null,
StudentID int not null,
constraint pk_ca primary key (GroupID, StudentID))

Just add attributes to the students and groups table as needed. Create as many groups as you need. also change names of groups and/or students easily without cascading updates.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-26 : 10:15:50

That's what table 2 is already (Student Groups). You would need to get all rows in table one where GroupName = 'WEST', you then need to go to table2 (StudentGroups) and get the ids where it is in a coorelation for the above. Problem is, you will have more than House and Class (i would imagine) in the group table. So then how do you know which rows to pull?

I offed the Views example as a soultion to isolate like things in to their own "virtual" buckets.

So that:
SELECT * FROM Class Where GroupId In (
SELECT GroupId From Table2
WHERE StudentId IN(
SELECT StudentId FROM House a, Table2 b
WHERE a.GroupId = b.GroupId And GroupName = 'WEST')))

Or am I totally missing the point.

To me it doesn't seem very straight forward.

Brett

8-)

MichaelG
Starting Member

11 Posts

Posted - 2003-03-26 : 14:02:53

I've come up with a solution (pretty close to Brett's I think) which seems to do what I want. I've no idea whether it is optimal, my T-SQL skills are fairly limited. A slightly cut down version follows :-

SELECT H.StudentID, Class=T.GroupName, H.Forename, H.Surname
FROM (
-- Select everyone in House 'West'
SELECT S.StudentID, S.Forename, S.Surname
FROM Table1 as T join Table2 as N on T.GroupID = N.GroupID
join Students as S on S.StudentID = N.StudentID
WHERE T.GroupType = 'House' and T.GroupName = 'West')
-- now get a match for type = 'Class'
as H join Table2 as A on H.StudentID = A.StudentID
LEFT join Table1 as T on T.GroupID = A.GroupID
WHERE T.GroupType = 'Class'
ORDER BY H.Surname

I'm still interested to know whether I could have organised the data better. The object is to assign an arbitrary number of user-definable groups to each student.

Students table holds students - StudentID, StudentNames
Table1 holds Groups - GroupType, GroupName, GroupID
Table2 joins them - GroupID, StudentID

It seems to me that this is about as normalised as I can get.

With this arrangement I can display a tree which allows access to students via any of the user-defined groups but also allow printouts of the form, 'print a House list showing each student's class'.

Does this seem alriaght? Any thoughts?

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources