Problems with firing query on a database table in phpMyAdmin. Obtaining 0 rows as result

Question

I am using phpMyAdmin for feeding data and I ran into a problem. I am not much experienced with normalisation of data. But I am trying a few stuff out.

I have some tables in my database as shown below:

-- Table name `Universities`
SrNo  ||  University
-----     ----------
 1         Harvard
 2         Boston
 3         Oxford
 4         Stanford 
 5         Dublin

I created the above table as:

CREATE TABLE Universities (

    SrNo INT PRIMARY KEY AUTO_INCREMENT,
    University LONGTEXT NOT NULL
);

Now I have another table as:

--Table name `Courses`
SrNo  ||  Course
----      ------
  1       Maths
  2       Physics
  3       Computer Science
  4       Electronics
  5       Chemistry

I created the above table as:

CREATE TABLE Courses (

    SrNo INT PRIMARY KEY AUTO_INCREMENT,
    Course LONGTEXT
);

Now the problem is that one university offers n number of courses where n may or may not be equal for each university. And each course is offered by m number of universities where m may or may not be equal for each subject.

For example:

Harvard --> Maths, Electronics, Physics                 (3 subjects)
Boston  --> Maths, Computer Science                     (2 subjects)
Stanford -> Chemistry, Electronics, Computer Science    (3 subjects)
Oxford  --> Physics, Chemistry, Maths, Computer Science (4 subjects)
Dublin  -->                                             (0 subjects)

As there is no way of firing a JOIN query(as per my knowledge LIMIT) to extract the name of university along with the courses they offer, so as per my teachers advice (or maybe a hint), what I did is I created another table which will store references of Universities.SrNo and Courses.SrNo.

SrNo  ||  UnivID  ||  CourseID
----      ------      --------
  1          1            1    -- This means Harvard(SrNo=1 in table `Universities`) offers Maths(SrNo=1 in table `Courses`)
  1          1            4
  1          1            2
  1          2            1
  1          2            3
  1          4            5
  1          4            4
  1          4            3
  1          3            2
  1          3            4
  1          3            1
  1          3            3
  1          5          null

I created the above table as:

CREATE TABLE Reference (

    SrNo INT PRIMARY KEY AUTO_INCREMENT,
    UnivID INT NOT NULL,
    CourseID INT,      -- m not giving this a not null constraint coz a university may offer none of the course mentioned in the table `Courses`
);

With the help of this, I am trying to extract the University name and the Courses name they offer.

Like if I want to list all courses offered by Harvard, this is what I do:

SELECT Universities.University, Courses.Course FROM Universities, Courses, Reference
WHERE Universities.University="Harvard" AND Reference.UnivID = Universities.SrNo

But I get 0 rows as a result.

What should I do?
1> Is there any way to get this result without the table named Reference:

Harvard    Maths
Harvard    Electronics
Harvard    Physics

2> If yes, then how? If no, the how should I build my SQL query to get the above mentioned result in 1?

Prakhar Asthana · Accepted Answer · 2014-11-26 16:41:51Z

1

You can solve this by nested query :

Select U.University,C.Course from Universities U, Course C where C.SrNo in (
Select R.CourseID from Reference R where UnivId in (select U1.SrNo from Universities U1 where U1.University ="Stanford" ))

answered Nov 26, 2014 at 16:41

Prakhar Asthana

915 bronze badges

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

4 Comments

Aditya Singh Over a year ago

Is there no simple way of doing it? Can you please explain how this works?

Prakhar Asthana Over a year ago

@Aditya You first try subdivide the question and basis of that structure the nested queries. In this case select U1.SrNo from Universities U1 where U1.University ="Stanford" will fetch the ID of university Stanford now on the basis of this ID you can fetch courseID from Reference Table and ultimately will achieve University Course pairs. Always to read nested queries inner part first and correlate it with outer query this will definitely help you to read as well as understand nested queries

Aditya Singh Over a year ago

Ok. One last question. What does the in clause do? What is is used for?

Prakhar Asthana Over a year ago

@Aditya In SQL "in" clause used to check whether the columns value contains any among the following set of value i.e For example : you want a name of a persons who are having ages (21 22 23 24 ...) so you will write your SQL something like ' select name from Employee where age in (21,22,23,.....)' . For more clarification : General Syntax is 'SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);' refer w3schools.com/sql/sql_in.asp url.

Collectives™ on Stack Overflow

Problems with firing query on a database table in phpMyAdmin. Obtaining 0 rows as result

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related