[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 s.date;

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


-- 
Kristof