mysql - query group/concat question??
- by tom smith
I have a situation where I return results with mutiple rows. I'm looking for aw way to return a single row, with the multiple items in separate cols within the row. My initial query:
SELECT a.name, a.city, a.address, a.abbrv, b.urltype, b.url
FROM jos__universityTBL as a
LEFT JOIN jos__university_urlTBL as b on b.universityID = a.ID
WHERE a.stateVAL = 'CA'
...my output:
| University Of Southern Califor | Los Angeles | | usc | 2 | http://web-app.usc.edu/ws/soc/api/ |
| University Of Southern Califor | Los Angeles | | usc | 4 | http://web-app.usc.edu/ws/soc/api/ |
| University Of Southern Califor | Los Angeles | | usc | 1 | www.usc.edu |
| San Jose State University | San Jose | | sjsu | 2 | http://info.sjsu.edu/home/schedules.html |
| San Jose State University | San Jose | | sjsu | 4 | https://cmshr.sjsu.edu/psp/HSJPRDF/EMPLOYEE/HSJPRD/c/COMMUNITY_ACCESS.CLASS_SEARCH.GBL?FolderPath=PORTAL_ROOT_OBJECT.PA_HC_CLASS_SEARCH |
| San Jose State University | San Jose | | sjsu | 1 | www.sjsu.edu
My tbl schema...
mysql> describe jos_universityTBL;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| name | varchar(50) | NO | UNI | | |
| repos_dir_name | varchar(50) | NO | | | |
| city | varchar(20) | YES | | | |
| stateVAL | varchar(5) | NO | | | |
| address | varchar(50) | NO | | | |
| abbrv | varchar(20) | NO | | | |
| childtbl | varchar(200) | NO | | | |
| userID | int(10) | NO | | 0 | |
| ID | int(10) | NO | PRI | NULL | auto_increment |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
mysql> describe jos_university_urlTBL;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| universityID | int(10) | NO | | 0 | |
| urltype | int(5) | NO | | 0 | |
| url | varchar(200) | NO | MUL | | |
| actionID | int(5) | YES | | 0 | |
| status | int(5) | YES | | 0 | |
| ID | int(10) | NO | PRI | NULL | auto_increment |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
I'm really trying to get something like:
|<<the concated urltype-url >>|
ucs | losangeles | usc.edu | 1-u1 | 2-u2 | 3-u2 |
Any pointers would be helpful!
