Hans,
On 2/4/02 6:30 PM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
> From: hz11 <[EMAIL PROTECTED]>
> Date: Mon, 04 Feb 2002 21:17:15 -0500
> To: [EMAIL PROTECTED]
> Subject: Dealing with One-to-Many SELECTS (MySQL)
>
>
>....
> to get a single event. This all works fine, however this is the result
> set I get back:
>
> 45 48 1012359206 1012134660 1 Jan
> 45 48 1012359206 1012134660 2 27
> 45 48 1012359206 1012134660 61 07
> 45 48 1012359206 1012134660 39 31
> 45 48 1012359206 1012134660 5 00
> 45 48 1012359206 1012134660 6 ahost
> 45 48 1012359206 1012134660 69 14823
> 45 48 1012359206 1012134660 10 my.host.com
>
> My question is there any way to consolidate the rows, so that I avoid
> the redundant data?
According to the DBMS there is no redundant data in the above results set.
Yes we see the first 4 columns are identical but because the last two are
not the same the set contains no duplicate rows (commands like "DISTINCT and
GROUOP BY will not do what you wish).
> In other words:
>
> 45 48 1012359206 1012134660 1 Jan 2 27 61 07 39 31 5 00 6 ahost 69 14823
> 10 my.host.com
This cannot be done with a single select statement - SQL results are very
square (think excel spread sheet) every row will have every column and you
cannot have a single row / column with distinct values from multiple rows.
Even if you're talking about aggregate functions (average, count, min, max)
these aren't distinct values.
> I could do this in multiple queries,
That's the way I'd do it.
> which works fine when there is only
> one event returned, but if I query on a different field (event.tid for
> example, which is a type of line) things get very messy.
Try opening two DB connections - you've got one working already
$res1 has the result of
SELECT event.eid, event.tid, event.itimestamp, event.etimestamp
FROM event
WHERE event.eid='45' (I've left out the join and date table)
/* I'll assume ODBC but it works for any type */
While(odbc_fetch_row($res1))
{
$eid = odbc_result($res1,1);
/* Print or whatever you want to do with */
/* event.eid, event.tid, event.itimestamp, event.etimestam */
$sql2 = "SELECT data.did,data.data
FROM event LEFT JOIN line ON event.eid=line.eid
LEFT JOIN data ON line.did=data.did
WHERE event.eid=/"$my_eid/";
$res2 = odbc_exec($con2,$sql2);
While(odbc_fetch_row($res2))
{
/* now fetch and do what you want with the date.did and data */
$did = odbc_result($res2,1);
$data = odbc_result($res2,2);
}
}
Not so messy, No?
Good Luck,
Frank
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
