a possible group issue???

Hi...

I have the following...

mysql> INSERT INTO ParseScriptTBL VALUES
-> ('auburnCourse.py',40,1,1),
-> ('auburnFaculty.py',40,2,2),
-> ('uofl.py',2,1,3),
-> ('uky.py',3,1,4),
-> ('ufl.py',4,1,5)
-> ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from ParseScriptTBL as p join universityTBL as u on
u.ID=p.CollegeID where u.ID=40;
+------------------+-------+----------+--------+----+
| ScriptName | CollegeID | pTypeID | ScriptID | ID |
+------------------+-------+----------+--------+----+
| auburnCourse.py | 40 | 1 | 1 | 40 |
| auburnFaculty.py | 40 | 2 | 2 | 40 |
+------------------+-----------+------+--------+----+
2 rows in set (0.00 sec)


i'd like to have a query that gives me both scripts for the college in the
same row...
keeping in mind that some colleges will have no scripts, some will have only
one, and some will have both...

i've tried to do the query, and added a "group by CollegeID' with no luck..

so how can i combine the two rows to get a single row??


thanks



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: a possible group issue???

--0016369cff73803c0d046c2b1309
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Try with GROUP_CONCAT(ScriptName)

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.ht ml#function_group-concat



2009/6/12 bruce

> Hi...
>
> I have the following...
>
> mysql> INSERT INTO ParseScriptTBL VALUES
> -> ('auburnCourse.py',40,1,1),
> -> ('auburnFaculty.py',40,2,2),
> -> ('uofl.py',2,1,3),
> -> ('uky.py',3,1,4),
> -> ('ufl.py',4,1,5)
> -> ;
> Query OK, 5 rows affected (0.00 sec)
> Records: 5 Duplicates: 0 Warnings: 0
> mysql> select * from ParseScriptTBL as p join universityTBL as u on
> u.ID=p.CollegeID where u.ID=40;
> +------------------+-------+----------+--------+----+
> | ScriptName | CollegeID | pTypeID | ScriptID | ID |
> +------------------+-------+----------+--------+----+
> | auburnCourse.py | 40 | 1 | 1 | 40 |
> | auburnFaculty.py | 40 | 2 | 2 | 40 |
> +------------------+-----------+------+--------+----+
> 2 rows in set (0.00 sec)
>
>
> i'd like to have a query that gives me both scripts for the college in the
> same row...
> keeping in mind that some colleges will have no scripts, some will have
> only
> one, and some will have both...
>
> i've tried to do the query, and added a "group by CollegeID' with no luck..
>
> so how can i combine the two rows to get a single row??
>
>
> thanks
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=maxbube@gmail.com
>
>

--0016369cff73803c0d046c2b1309--

RE: a possible group issue???

hi martin...

thanks for the reply.. but that still generates two separate rows as well...


-----Original Message-----
From: Martin Gainty [mailto:mgainty@hotmail.com]
Sent: Friday, June 12, 2009 12:04 PM
To: bruce Douglas
Subject: RE: a possible group issue???


mysql> select * from ParseScriptTBL as p join universityTBL as u on
u.ID=p.CollegeID where u.ID=40
GROUP BY CollegeID WITH ROLLUP
;

http://dev.mysql.com/doc/refman/6.0/en/group-by-modifiers.ht ml

Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité


Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
de ceci est interdite. Ce message sert à l'information seulement et n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les email
peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
aucune responsabilité pour le contenu fourni.





> From: bedouglas@earthlink.net
> To: mysql@lists.mysql.com
> Subject: a possible group issue???
> Date: Fri, 12 Jun 2009 11:36:35 -0700
>
> Hi...
>
> I have the following...
>
> mysql> INSERT INTO ParseScriptTBL VALUES
> -> ('auburnCourse.py',40,1,1),
> -> ('auburnFaculty.py',40,2,2),
> -> ('uofl.py',2,1,3),
> -> ('uky.py',3,1,4),
> -> ('ufl.py',4,1,5)
> -> ;
> Query OK, 5 rows affected (0.00 sec)
> Records: 5 Duplicates: 0 Warnings: 0
> mysql> select * from ParseScriptTBL as p join universityTBL as u on
> u.ID=p.CollegeID where u.ID=40;
> +------------------+-------+----------+--------+----+
> | ScriptName | CollegeID | pTypeID | ScriptID | ID |
> +------------------+-------+----------+--------+----+
> | auburnCourse.py | 40 | 1 | 1 | 40 |
> | auburnFaculty.py | 40 | 2 | 2 | 40 |
> +------------------+-----------+------+--------+----+
> 2 rows in set (0.00 sec)
>
>
> i'd like to have a query that gives me both scripts for the college in the
> same row...
> keeping in mind that some colleges will have no scripts, some will have
only
> one, and some will have both...
>
> i've tried to do the query, and added a "group by CollegeID' with no
luck..
>
> so how can i combine the two rows to get a single row??
>
>
> thanks
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mgainty@hotmail.com
>



Insert movie times and more without leaving Hotmail®. See how.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org