JOINING Tables

[engrjd91]

I am trying a query in mysql. I have 3 tables which are linked by the same foriegn key ...

Table 1 = students (PKey - stdnt_rfid_tag)
Table 2 = student_attendance ( FKey - s_id for stdnt_rfid_tag )
Table 3 = users ( Fkey - s_id for stdnt_rfid_tag)

In the students table there is the name of the student which i am interested in.
In the student_attendance table i am interested in the classes attempt.
In the users table i am interested in the course name.

Now I want a query to display the courses which are assigned to the specific users..

Means a student with RFID_TAG = 101 is displayed with 2 courses, so only these two courses should be displayed.
But whenever i run my query it displays the complete list of courses with the assigned users present in the database table. please help..

Here is my query which i have tried.

SELECT s.stdnt_rfid_tag, c.course_name, sa.st_classes_attempt FROM students s INNER JOIN users u ON s.stdnt_rfid_tag = u.st_id INNER JOIN student_attendance sa ON s.stdnt_rfid_tag = sa.st_id INNER JOIN courses c ON sa.c_id = c.course_id

[diafol]

This is confusing. You don't mention the courses table until we read the query.

If you could dump the table structures here and maybe a couple of lines of sample data for each, it may help.

[engrjd91]

This post has no text-based content.

[engrjd91]

are these helpful ?

[engrjd91]

Dear i can do it by adding a where clause in the end like WHERE sa.st_id=1000;
But This would not be a dynamic thing to do. Because i am embedding this query in my php.. and for this purpose i would have to use multiple if statements for each records.

I want to have specific courses in my page for specific users using one query.

I hope you got my point.

[diafol]

Ok, that makes more sense. The naming of your tables are a bit odd though. There seems to be a lot of duplication here - if that's so, then you need to think about restructuring before proceeding.

You could probably get away with a single users table instead of users, teachers, students, student_user_login, teacher_user_login

user

user_id
official_no
firstname
lastname
title
usertype (1/2/..? = teacher/student/others..?)
login
passwordhash
email
tel
...

Once usertype is set for your users, you can then filter with usertype = 1 or usertype = 2, which automatically gives certain permissions to users or lists just students or just teachers for selecting.

Just a thought.

courses

course_id
course_title
course_code
(other fields...)

course_teacher

ct_id
course_id (FK on courses.course_id)
teacher_id (FK on users.user_id)
course_leader (0/1)

course_student

cs_id
course_id (FK on courses.user_id)
student_id (FK on users.user_id)
attendance_present
attendance_absent

Perhaps it would be easier?
The percentage attend could be a calculated field

[engrjd91]

Bro, actually there were less tables before but when i normalized my database to 3 NF , so i had to contruct these extra tables to store my attributes in the seperate tables. I liked the way you are asking me to design my database. But i want one more advice from you that is that okay to design this database just like this for a fully fledge web based attendance system? your precribed database is normalized to 2NF. is it okay?

[diafol]

our precribed database is normalized to 2NF. is it okay?

That was just a suggestion based on what I'd probably use - after a quick think - but you know the needs of your setup better than me. Only you can decide whether something is ok to use or not. It's your stuff after all.

Which parts do you think are causing a problem 2NF-wise?

You could of course get rid of the PKs suggested cs_id and ct_id and make PKs from the next two (combined) columns.