Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- trying to get a basic query that counts the entries in a subordinate tbl that's linked to the parent tbl
- my two tbls are::
- mysql> describe knews_university_resourceTBL;
- +----------------------------+-------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +----------------------------+-------------+------+-----+---------+----------------+
- | universityID | int(10) | NO | | 0 | |
- | resource_name_first | varchar(20) | NO | | | |
- | resource_name_last | varchar(20) | NO | | | |
- | resource_email | varchar(20) | NO | UNI | | |
- | resource_phone | varchar(20) | NO | | | |
- | resource_title | varchar(20) | NO | | | |
- | resource_compensation_rate | int(5) | NO | | 0 | |
- | resource_hours_per_week | int(5) | NO | | 0 | |
- | resource_calls_per_week | int(5) | NO | | 0 | |
- | ID | int(10) | NO | PRI | NULL | auto_increment |
- +----------------------------+-------------+------+-----+---------+----------------+
- 10 rows in set (0.00 sec)
- mysql> describe knews_universityTBL;
- +----------------+--------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +----------------+--------------+------+-----+---------+----------------+
- | name | varchar(100) | YES | UNI | NULL | |
- | repos_dir_name | varchar(50) | NO | | | |
- | city | varchar(50) | YES | | NULL | |
- | stateVAL | varchar(5) | NO | | | |
- | address | varchar(50) | NO | | | |
- | zipcode | varchar(20) | YES | | NULL | |
- | abbrv | varchar(50) | NO | | | |
- | enrollment | int(10) | YES | | NULL | |
- | college_type | int(1) | YES | | NULL | |
- | schedule_type | int(1) | YES | | NULL | |
- | childtbl | varchar(200) | NO | | | |
- | userID | int(10) | NO | | 0 | |
- | ID | int(10) | NO | PRI | NULL | auto_increment |
- +----------------+--------------+------+-----+---------+----------------+
- 13 rows in set (0.00 sec)
- select name,resource_email from knews_universityTBL as u1
- left join knews_university_resourceTBL as uR
- on uR.universityID=u1.ID
- where stateVAL='CA';
- gets me:::::
- name email
- | University of California-Merced | NULL |
- | University of California-Riverside | NULL |
- | University of California-San Diego | NULL |
- | University of California-Santa Cruz | NULL |
- | University of Northern California | NULL |
- | University of Southern California | tom@yahoo.com |
- | University of Southern California | bob@yahoo.com |
- | University of Southern California | sue@yahoo.com |
- | University of West L A Schs of Law/P | NULL |
- | University of California-Davis | NULL |
- |
- i'd like to get something like::
- | University of California-Merced | NULL |
- | University of California-Riverside | NULL |
- | University of California-San Diego | NULL |
- | University of California-Santa Cruz | NULL |
- | University of Northern California | NULL |
- | University of Southern California | 3 |
- | University of West L A Schs of Law/P | NULL |
- | University of California-Davis | NULL |
- so i'm actually counting the number of resource emails per college, so i only display
- the count..
- but i'm not sure of what the correct query would be??
- i've tried different queries with group/group_concat/count... but seem to be missing something..
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement