mysql - query group/concat question??

Posted by tom smith on Stack Overflow See other posts from Stack Overflow or by tom smith
Published on 2010-04-02T19:03:22Z Indexed on 2010/04/02 19:23 UTC
Read the original article Hit count: 703

Filed under:
|
|
|
|

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!

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql