[thelist] complicated sql

[Kristof Neirynck]

> I have three tables.  it looks something like this:
> (the site is for a venue/club)
>
> BANDS
> band_id | band_name
> SHOW INFO
> show_id | show_date | band_id (same id as the bands.band_id)
> PHOTOS
> photo_id | photo_name | photo_bandid (same id as the bands.band_id)
>
> How do I write a query to retrieve BANDS.band_name, SHOWINFO.show_date,
> and PHOTOS.photo_name?

1. Look at your tables again.
The data you're looking for is not stored in them.
There is no way to link a picture to a band and a show.

2. Alter the database
bands  : id (PK), name
shows  : id (PK), date
photos : id (PK), name, band (FK), show (FK)

3. Set up a new query.
SELECT b.name
,      s.date
,      p.name
FROM   bands AS b
,      shows AS s
,      photos AS p
WHERE  s.id = p.show
AND    b.id = p.band
ORDER BY shows.date;

4. Test it.
I didn't, so it might be flawed.


-- 
Kristof