----- Original Message -----
From: "Karl-Heinz Schulz" <[EMAIL PROTECTED]>
To: "'Philippe Poelvoorde'" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, August 04, 2004 6:41 AM
Subject: RE: SQL Syntax Question
> Philippe,
>
> I changed my to the following but the result is now (I deleted the print
> stuff for better reading)
>
> <?
> $event_query = mysql_query("select id, inserted, information, eventname,
> date, title from event order by inserted desc LIMIT 0 , 30");
> while($event = mysql_fetch_row($event_query)){
>
>
> $eventdetail_query = mysql_query("select titles, informations, file_name
> from eventdetail, event where event.id=eventdetail.event AND
> event.id=".$event[0]);
>
> while($eventdetail = mysql_fetch_row($eventdetail_query)){
>
> }
> }
>
> ?>
>
Karl-Heinz,
I used the following SQL in a script and got the answer that I think you
want:
select informations, titles, file_name
from eventdetail d inner join event e on e.veranastaltung = d.event
where d.event = 1
This gave me just the eventdetails for event 1.
This is not in php format of course. I don't know php but it looks similar
to other languages I know so I'm guessing that you would write it as follows
in php:
> $eventdetail_query = mysql_query("select titles, informations, file_name
> from eventdetail d inner join event e on e.veranstaltung = d.event
> where event.id=".$event[0]);
Explanation:
Since you named two tables in the 'from' clause of the eventdetail query,
you are clearly attempting to join the tables. I'm assuming you want an
inner join. In other words, you only want to show details if there is a
corresponding event row that matches your detail row. To get a proper join,
you need to identify what the two tables have in common. If I understand
your data correctly, the veranstaltung column in the Event table is going to
have the same value as the event column in the Eventdetail table when the
rows are describing the same event. Therefore, that is what I put in the
'on' clause of the query. The 'where' clause is the one I'm least sure how
to write in php but, based on what you had in your queries, I assume that
this is the way to tell the query to return only rows where the event column
in the join result has the same value as the event value in the event row
currently being processed in the outer loop.
In short, you were doing a join implicitly but hadn't properly specified the
joining condition so you weren't getting the rows you really wanted.
By the way, I really wasn't completely clear on the meaning of the data in
the tables so I made some guesses about the contents of each column. This is
the script I wrote to create and populate the tables. Your original event
query, which is unchanged, appears after that and my best guess for the
eventdetail query is at the end.
----------------------------------------------------------------------------
---------------------------------
use tmp;
#Event table contains one row for each event.
select 'Drop/create Event table';
drop table if exists event;
create table if not exists event
(id smallint(2) unsigned not null auto_increment,
veranstaltung smallint(2) not null default '0',
inserted date not null default '0000-00-00',
information text not null,
eventname text not null,
date varchar(30) not null default '',
title varchar(100) not null default '',
primary key(id)
) TYPE=MyISAM;
select 'Populate Event table';
insert into event (veranstaltung, inserted, information, eventname, date,
title) values
(1, '2004-04-20', 'information-01', 'Canada Day', '2004-07-01', 'title-01'),
(2, '2004-05-03', 'information-02', 'Labour Day', '2004-09-04', 'title-02'),
(3, '2004-08-15', 'information-03', 'Christmas Day', '2004-12-25',
'title-03');
select 'Display Event table';
select * from event;
#Event_Detail table contains one row for each aspect of an event.
select 'Drop/create Eventdetail table';
drop table if exists eventdetail;
create table if not exists eventdetail
(id smallint(2) unsigned not null auto_increment,
event smallint(2) not null default '0',
informations text not null,
titles varchar(100) not null default '',
file_name varchar(100) not null default '',
primary key(id)
) TYPE=MyISAM;
select 'Populate Eventdetail table';
insert into eventdetail (event, informations, titles, file_name) values
(1, 'information-01a', 'title-01a', 'file-01a'),
(1, 'information-01b', 'title-01b', 'file-01b'),
(1, 'information-01c', 'title-01c', 'file-01c'),
(2, 'information-02a', 'title-02a', 'file-02a'),
(2, 'information-02b', 'title-02b', 'file-02b'),
(2, 'information-02c', 'title-02c', 'file-02c'),
(3, 'information-03a', 'title-03a', 'file-03a'),
(3, 'information-03b', 'title-03b', 'file-03b'),
(3, 'information-03c', 'title-03c', 'file-03c');
select 'Display Eventdetail table';
select * from eventdetail;
select 'Event query';
select id, inserted, information, eventname, date, title
from event
order by inserted desc limit 0, 30;
select 'Eventdetail query';
select informations, titles, file_name
from eventdetail d inner join event e on e.veranstaltung = d.event
where d.event = 1;
----------------------------------------------------------------------------
----------------------------------
I hope this helps!
Viel Gluck!
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
