Getting data from multiple tables using joins

Question

I have various tables like

Student
primary id , students name, course

Papers
paper id, papername, course, semester, type

StudentOptions
primary id, studentid (foreign key - reference student id) and paperid (foreign key - references paper id)

StudentsTerm
studentid (foreign key- references student id) and student semester

Now the kind of result i want is,

I want to choose a course then the term, which will give me the number of papers/subject it has with their types (Mandatory/Optional) and with that i want to have the count of number of students studying those papers from all these tables.

I don't wanna create any view or stuff, Just a normal select query will do.

The query i am running is :

SELECT p_name,
       p_id,
       type,
       Count(sps.studentid) AS counts
FROM   students,
       str,
       papers
       LEFT JOIN sps
              ON sps.paperid = papers.p_id
WHERE  sps.studentid = students.studentid
       AND students.studentid = str.studentid
       AND sps.studentid = str.studentid
       AND str.semesterid = p_semid
       AND str.sessionid = 12
       AND students.course = c_id
       AND c_id = 6
       AND p_semid = 1
GROUP  BY p_id

It is giving me only those papers in which student are studying. I want all the papers for that given semester and course and if there aren't any studying then it should give me zero. — I Perfect
– I Perfect, Commented Oct 29, 2012 at 19:25

Kermit · Accepted Answer · 2012-10-29 19:44:18Z

2

As better practice, if you are going to be using explicit JOIN syntax, then don't use implicit syntax. In the query you posted above, you select from papers, but you don't use it anywhere. Also, your column names are slightly ambiguous. If it's easier, alias each table using a single letter, or explicitly prefix the column names. If you're using an aggregate with GROUP BY, you cannot select columns which are not in the group.

Let's assume this is your ER diagram:

Let's first join all the tables:

SELECT a.id, a.name
FROM student a
JOIN str b ON b.student_id = a.id
JOIN sps c ON c.student_id = a.id
JOIN papers d ON d.id = c.paper_id

Now you wish to find the number of students studying papers from a specific course and type:

SELECT a.id, a.name
FROM student a
JOIN str b ON b.student_id = a.id
JOIN sps c ON c.student_id = a.id
JOIN papers d ON d.id = c.paper_id
WHERE b.semester = 12
    AND d.course = 6

Because your attributes are ambigiuous, it is hard to tell what tables they are coming from. If you can set up the structure and sample data on SQL Fiddle, we could help you better.

edited Oct 29, 2012 at 19:44

answered Oct 29, 2012 at 19:27

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

I Perfect Over a year ago

I want to have papers name from papers, this is why am using it.

Kermit Over a year ago

@IPerfect Here is a query that join's all the tables. Do you want to try filtering your result?

I Perfect Over a year ago

This is what i dont want . I want all the papers from papers table depending on the course and the number of student currently studying it.

I Perfect Over a year ago

check this out. sqlfiddle.com/#!2/a910b/6 . this is the kind of result i want but i want to add in studentterm and studentcourse table as well.

I Perfect Over a year ago

this is not the result i want. I also want maths II with 0 students be shown in the result.

|

Collectives™ on Stack Overflow

Getting data from multiple tables using joins

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related