[thelist] complicated sql

[Joel D Canfield]

> How do I write a query to retrieve BANDS.band_name, 
> SHOWINFO.show_date, and PHOTOS.photo_name?  I had this:

You need a join:

     select 
       bands.band_name
     , showinfo.show_date
     , photos.photo_name
     from
     bands
left 
     join
     showinfo
     on
     bands.band_id = showinfo.band_id
left 
     join
     photos
     on
     bands.band_id = photos.photo_bandid

(and you could include filtering:)

     where bands.band_name = 'Shellshock Rome'
     and showinfo.show_date between #2004-02-07# and #2004-04-30#

You can write it all out on one line as:

select bands.band_name , showinfo.show_date , photos.photo_name from
bands left join showinfo on bands.band_id = showinfo.band_id left join
photos on bands.band_id = photos.photo_bandid

but I've grown fond of rudy's layout for easy troubleshooting

joel