CODEWITHSUNDEEP
phpmysqlsql
JSking
JSking

Reputation: 419

how to count rows in table that has specific property in another table

table1
--------------
| sn | class |
--------------

table2
----------------
| id | student |
----------------

all are int as sn is table1 is linked to student in table2 sn, id are auto increasing. when inserting data to table2 student column is same as sn in table 1

now I want to select student in table2 but only those whose class in table1 is "3" my syntax is thus;

$count = mysql_query(SELECT student from table2 whose class in table1 =3)

so that i can count them by saying

$quantity = mysql_num_rows($count)

now my problem is if sql also have this whose keyword, or how do i go about this.

$count = mysql_query(SELECT student from table2 whose class in table1 =3)

Upvotes: 0

Views: 77

Answers (2)

Paul
Paul

Reputation: 9022

You need to join the tables in order to filter the results properly.

(1) This will give you the number of students for class 3.

$count = mysql_query(
  'SELECT COUNT(t2.student) 
   FROM table2 t2 
   INNER JOIN table1 t1
     ON t1.sn = t2.student
     AND t1.class = 3'
);

(2) This will give you all classes and the number of students for each.

$count = mysql_query(
  'SELECT t1.class, COUNT(t2.student) 
   FROM table2 t2 
   INNER JOIN table1 t1
     ON t1.sn = t2.student
   GROUP BY t1.class
   ORDER BY t1.class'
);

(3) This will give you all classes and the students list.

$list = mysql_query(
  'SELECT t1.class, GROUP_CONCAT(t2.student SEPARATOR ',') 
   FROM table2 t2 
   INNER JOIN table1 t1
     ON t1.sn = t2.student
   GROUP BY t1.class
   ORDER BY t1.class'
);

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

You should join those two tables and limit your result to those which have table1.class = 3

SELECT
    student
FROM
    table2 a
    JOIN table1 b ON (a.student = b.sn)
WHERE b.class = 3

If you want a count you could also do it through SQL by using aggregate function

SELECT
    COUNT(student)
FROM
    table2 a
    JOIN table1 b ON (a.student = b.sn)
WHERE b.class = 3

Upvotes: 0

Related Questions