Outer join Query help

many to many

[txbakers]

SELECT name,school,head,auth,count(id) as stus,max(logindate)as logindate from users,schoolinfo left outer join students on schoolinfo.schcode=students.schcode left outer join logintable on users.user = logintable.user where users.schcode = schoolinfo.schcode group by name, head ,auth,school order by school, logindate desc

In the query above, the logintable has many records, even for the "max" command, so my count(id) is skewed based on the the number of times the user is in the logintable.

Is there a better way to write this query? I thought about a subselect in the Select clause, but couldn't figure that one out, given the outer join nature.

Thanks.

[TheNige]

What is the count counting? The number of students in the school? Can you give us the schema of the tables?

[txbakers]

Thanks Nige.

The count is the number of students for that one schcode.

The schemas are fairly large so I'll summarize:

users:
user
pwd
schcode
name
head
schoolinfo:
schcode
school
auth

students:
id
schcode

logintable:
user

users > logintable is one to many
schoolinfo > users is one to many
schoolinfo > students is one to many

Hope that clears it up.

[aspdaddy]

Looks like you are selecting for each school, the number of students and the last login date for any user.

If thats it, I would use inner joins and just group by school, then count isnt affected by the number of rows in login table

FROM ((schoolinfo INNER JOIN students ON schoolinfo.schcode = students.schcode) INNER JOIN users ON schoolinfo.schcode = users.schcode ) INNER JOIN logintable ON users.user = logininfo.user
group by schoolinfo.school
order by school, logindate desc

[txbakers]

Thanks, but they might not have any students or have even logged in, so I need the outer joins.

What I'm getting is the cross join between count()students and number of logins for that last day.

So, if I logged in 10 times today, and have 15 students, the query is returning 150 students.

[aspdaddy]

I see.
So school and students need the left outer join as you have done already.

The max(empty_set) is a problem. Rather than use expesive cartesian product or suqueries,I would be tempted solve it with a an insert trigger on users, to insert into logintable a default "01-01-1901" whenver a user is created.

You could then create a view that restricts using "where logindate >01-01-1901", or substitutes it for 'None' using a CASE statement in the query, or just handle it in the application.

Thats one way, not a great one actually :) Im sure there are other ways.

[txbakers]

is there a way to limit the login table to 1 result?

The issue is not with the NULL in the logintable, but with the many rows for the MAX(date). If there are 10 entries for MAX(Date) I get 10 rows from the logintable.

Tricky one.

[aspdaddy]

If there are no logins, for a particular school, what should it return for max(logindate)?

[txbakers]

null

[aspdaddy]

OK,this seems to do the trick.

Users is inner joined with logins as if there are no users there cant be any logins so you just get an enmpty set, not null.

That join is then is then right outer joined to schools so that you still get a row for every school. So the empty set from the first query is then casted by the db server, in SQL2K this gives NUll.

Schools is then outer joined on the other side to students,again to get a row for every school.

Just group by School as you want 1 row per schoolID.

Users INNER JOIN
Logins ON Users.UserID = Logins.UserID
RIGHT OUTER JOIN
Schools ON dbo.Users.SchoolID = Schools.SchoolID LEFT OUTER JOIN
Students ON Schools.SchoolID = Students.SchoolID
GROUP BY Schools.SchoolID

[aspdaddy]

A good way to solve complex queries like these is with views. You can break it down into smaller tasks, in this case a query for counting students, a query for determining the max login date and then a master query to use the two sub queries.

You can test each bit and it simplifies the main query.

CREATE VIEW StudentCount
AS
SELECT School.SchoolID, COUNT(Students.StudentID) AS Students
FROM School LEFT OUTER JOIN
 Students ON School.SchoolID = Students.SchoolID
GROUP BY School.SchoolID

CREATE VIEW LoginDates
AS
SELECT MAX(Logins.LoginDate) AS MaxLoginDate, Users.UserID, Users.SchoolID
FROM Users INNER JOIN Logins ON Users.UserID = Logins.UserID
GROUP BY Users.UserID, Users.SchoolID

CREATE VIEW MasterQuery
AS
SELECT StudentCount.SchoolID, StudentCount.Students, LoginDates.MaxLoginDate AS MaxLoginDate
FROM LoginDates RIGHT OUTER JOIN
 StudentCount ON LoginDates.SchoolID = StudentCount.SchoolID

[txbakers]

both excellent ideas. I'm not sure I can use views with mySQL 4.01 but I'll try. I can always make a temp table and delete it.

I'l let you know which works.