mysql join problem

[geroid]

Hi
I have two tables that look like this:

EVENTS:
event_num
event_name
venue_name
streetaddress1
town
county
event_description_ire
event_description_eng
event_date
event_time

IMAGES:
image_id
event_id
category
image_path

There are event records in the EVENTS table and associated images in the IMAGES table. The two tables are linked on the EVENTS.event_num and IMAGES.event_id fields. What I want is an sql statement that retrieves one record from the EVENTS table and all of the associated image records from the IMAGES table. Then the next record from the EVENTS tables and all of it's images and so on.I'm finding this quite hard to do. I wrote an sql statement within an sql statement to do this but it didn't work. Then I tried a join but it retrieves an EVENT record and keeps repeating it on the page for every image it finds.
I suppose what I need is for an event record to be found and displayed on the page and then for all of the associated image records to be found and displayed before it goes to the next event record. Any ideas?

 

[r937]

What I want is an sql statement that retrieves one record from the EVENTS table and all of the associated image records from the IMAGES table. Then the next record from the EVENTS tables and all of it's images and so on.

a simple join


yes, the event data "repeats" on multiple rows if the event has multiple pictures

you would detect this using your front-end language (e.g. php) and format the results accordingly





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[geroid]

Thanks r937
I solved the problem by searching the database for an event record and also searching the images table within the event search as a loop. So now it finds an event record and then loops through the images table to match any records for that event before it moves on to a new event record and loops through the images table again. It works fine now. I always wonder if I'm writing the code properly but this works well so as they say "if it aint broke don't fix it"

Thanks again

 

[r937]

it's broke, but you won't have to fix it until your web site dies because it's so slow

a query inside a loop is very inefficient

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon