Count a separate item for each record and update it

advertisements

This my student Record table and i'm trying to find for each student, the number of other students with the same first or last name and save it in samefirst and samelast for each student.

   +-----------+-------------+------+-----+---------+-------+
   | Field     | Type        | Null | Key | Default | Extra |
   +-----------+-------------+------+-----+---------+-------+
   | first     | varchar(20) | YES  |     | NULL    |       |
   | last      | varchar(25) | YES  |     | NULL    |       |
   | samefirst | int(11)     | YES  |     | NULL    |       |
   | samelast  | int(11)     | YES  |     | NULL    |       |
   +-----------+-------------+------+-----+---------+-------+

select a.first as af, a.last as al, b.first as bf, b.last as bl
from StudRec a inner join
     StudRec b
     on a.first = b.first and a.last <> b.last) or
        a.first <> b.first and a.last = b.last;

The query above gives me each student and his matching first or last with another students but I'm having difficulties doing the count for each student and insert it into samefirst and samelast. Thanks in advace


Use correlation subquery to find for each student, students with similar first name or last name.

SELECT SR.first,
       SR.last,
       (SELECT COUNT(*) FROM StudRec AS SR2
        WHERE SR2.first = SR.first) AS samefirst,
       (SELECT COUNT(*) FROM StudRec AS SR2
        WHERE SR2.last = SR.last) AS samelast
FROM StudRec AS SR