I guess you are stuck building your list with application code, then. You
won't have to run a nested query. That would just overwork your server. You
can do it with a single query and a fairly quick piece of code. I have had
to write this routine at another job but not with PHP so please forgive the
pseudocode:
rs = the results of your query that returns one row per
speaker/presentation combination sorted by Presentation then Speaker
sOldPresentation=""
bFirst = true
(prepare some location for output. this could be a string array or a file
or a pipe or a socket or.... however you need to handle the results)
init(objOut)
if (rs.hasRecords()) {
rs.moveToFirstRecord()
while (rs.hasRecords()) {
sNewPresentation = rs.getField("Presentation")
if (sNewPresentation == sOldPresentation) {
sSpeakerList += ',' + rs.getField("Speaker")
} else {
if (bFirst) {
bFirst = false
} else {
objOut.write (sOldPresentation, sSpeakerlist)
}
sOldPresentation = sNewPresentation
sSpeakerList = rs.getField("Speaker")
}
rs.moveToNextRecord()
}
objOut.Write(sOldPresentation, sSpeakerList)
}
To summarize: start with the query results of Presentations and Speakers.
Begin crawling through the records . If the current record contains another
Speaker for the _same_ Presentation as the last record, just add the new
Speaker's name to the list, if it isn't (and it won't be for the very first
record) the commit to your output location the last Presentation and its
list of Speakers while starting a new list of Speakers for the current
Presentation. Continue crawling through your query results until you run
out of records then commit that last list you were building.
As I said, I haven't worked this solution in PHP or I could have given you
better code. I tried to keep it kind of java-ish/C-ish so I hope you can
translate it to PHP without much hassle. Its speed comes from the fact that
it only has to make 1 query and 1 pass through the results to build your
concatenated lists.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Matthias Kritz"
<[EMAIL PROTECTED] To: <[EMAIL PROTECTED]>
x.net> cc:
Fax to:
06/24/2004 01:33 Subject: RE: JOIN or UNION or both?
PM
Shawn,
Thank you for your reply.
That would have been an elegant solution but unfotunately Im not running
4.1 yet, which is required for GROUP_CONCAT() (no chance of an upgrade
either).
Is there a possibility of running perhaps two queries, one nested within
the other? This way I could pull out all presentation info first, and
then the relating speaker info (with a php loop through the recordset)?
Or could I do this with a temp table?
I dont know the in's and out's, so any help is greatly appreciated!
Thank you,
Matthias Kritz
_____________________
Digital Samba, S.L.
http://www.digitalsamba.com
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Donnerstag, 24. Juni 2004 17:25
> To: Matthias Kritz
> Cc: [EMAIL PROTECTED]
> Subject: Re: JOIN or UNION or both?
>
>
>
> Have you looked at the GROUP_CONCAT() function?
>
> http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html
>
> It returns a string result with the concatenated values from
> a group. If you didn't do this you would end up with a
> ragged-right data set that is just a nightmare to manage with
> SQL. In one row you would get 2 columns of data, in another
> you could have 10. This way you end up with two columns, one
> of them is a list of values.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
>
>
> "Matthias Kritz"
>
> <[EMAIL PROTECTED] To:
> <[EMAIL PROTECTED]>
> x.net> cc:
>
> Fax to:
>
> 06/24/2004 10:56 Subject: JOIN
> or UNION or both?
> AM
>
>
>
>
>
>
>
>
>
> Hi,
>
> I have the following table structure:
>
> tbl_speakers
> sid
> fname
> lname
>
> tbl_presentations
> pid
> name
> desc
>
> tbl_speakers_presentations
> sid
> pid
>
> I would like to display all presentations, (but each only
> once), and display the speakers' names for each presentation.
> With my current syntax (see below), if a presentation has,
> say, 3 speakers I receive 3 distinct records - I just want 1
> with 3 speakers columns.
>
> //PULL OUT EVERY PRESENTATION WITH A SPEAKER AS SEPERATE
> RECORD SELECT * FROM tbl_presentations, tbl_speakers,
> tbl_speakers_presentations WHERE tbl_speakers.sid =
> tbl_speakers_presentations.sid AND tbl_presentations.pid =
> tbl_speakers_presentations.pid
>
> I cant figure out the right JOIN or UNION or AS syntax -
> still learning SQL :-)
>
> Any help is greatly appreciated!
>
> Thank you,
>
> Matthias Kritz
>
> _____________________
> Digital Samba, S.L.
> http://www.digitalsamba.com
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
>
>
>
>
>
>
>
>
> --
> MySQL General Mailing
> List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
