Advertisement
Guest User

Untitled

a guest
Jun 4th, 2017
573
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.77 KB | None | 0 0
  1. trying to get a basic query that counts the entries in a subordinate tbl that's linked to the parent tbl
  2.  
  3. my two tbls are::
  4.  
  5. mysql> describe knews_university_resourceTBL;
  6. +----------------------------+-------------+------+-----+---------+----------------+
  7. | Field | Type | Null | Key | Default | Extra |
  8. +----------------------------+-------------+------+-----+---------+----------------+
  9. | universityID | int(10) | NO | | 0 | |
  10. | resource_name_first | varchar(20) | NO | | | |
  11. | resource_name_last | varchar(20) | NO | | | |
  12. | resource_email | varchar(20) | NO | UNI | | |
  13. | resource_phone | varchar(20) | NO | | | |
  14. | resource_title | varchar(20) | NO | | | |
  15. | resource_compensation_rate | int(5) | NO | | 0 | |
  16. | resource_hours_per_week | int(5) | NO | | 0 | |
  17. | resource_calls_per_week | int(5) | NO | | 0 | |
  18. | ID | int(10) | NO | PRI | NULL | auto_increment |
  19. +----------------------------+-------------+------+-----+---------+----------------+
  20. 10 rows in set (0.00 sec)
  21.  
  22. mysql> describe knews_universityTBL;
  23. +----------------+--------------+------+-----+---------+----------------+
  24. | Field | Type | Null | Key | Default | Extra |
  25. +----------------+--------------+------+-----+---------+----------------+
  26. | name | varchar(100) | YES | UNI | NULL | |
  27. | repos_dir_name | varchar(50) | NO | | | |
  28. | city | varchar(50) | YES | | NULL | |
  29. | stateVAL | varchar(5) | NO | | | |
  30. | address | varchar(50) | NO | | | |
  31. | zipcode | varchar(20) | YES | | NULL | |
  32. | abbrv | varchar(50) | NO | | | |
  33. | enrollment | int(10) | YES | | NULL | |
  34. | college_type | int(1) | YES | | NULL | |
  35. | schedule_type | int(1) | YES | | NULL | |
  36. | childtbl | varchar(200) | NO | | | |
  37. | userID | int(10) | NO | | 0 | |
  38. | ID | int(10) | NO | PRI | NULL | auto_increment |
  39. +----------------+--------------+------+-----+---------+----------------+
  40. 13 rows in set (0.00 sec)
  41.  
  42.  
  43. select name,resource_email from knews_universityTBL as u1
  44. left join knews_university_resourceTBL as uR
  45. on uR.universityID=u1.ID
  46. where stateVAL='CA';
  47.  
  48. gets me:::::
  49.  
  50. name email
  51. | University of California-Merced | NULL |
  52. | University of California-Riverside | NULL |
  53. | University of California-San Diego | NULL |
  54. | University of California-Santa Cruz | NULL |
  55. | University of Northern California | NULL |
  56. | University of Southern California | tom@yahoo.com |
  57. | University of Southern California | bob@yahoo.com |
  58. | University of Southern California | sue@yahoo.com |
  59. | University of West L A Schs of Law/P | NULL |
  60. | University of California-Davis | NULL |
  61. |
  62.  
  63.  
  64. i'd like to get something like::
  65.  
  66. | University of California-Merced | NULL |
  67. | University of California-Riverside | NULL |
  68. | University of California-San Diego | NULL |
  69. | University of California-Santa Cruz | NULL |
  70. | University of Northern California | NULL |
  71. | University of Southern California | 3 |
  72. | University of West L A Schs of Law/P | NULL |
  73. | University of California-Davis | NULL |
  74.  
  75. so i'm actually counting the number of resource emails per college, so i only display
  76. the count..
  77. but i'm not sure of what the correct query would be??
  78.  
  79. 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