[thelist] complicated sql

Joshua Olson joshua at waetech.com
Mon Dec 8 07:52:10 CST 2003


----- Original Message ----- 
From: "Comitque" <comitque at hotmail.com>
Sent: Sunday, December 07, 2003 10:28 PM


> 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?  I had this:
>
> SQLQuery = "select * from showinfo, bands, photos where (bands.band_id =
> photos.photo_bandid and showinfo.band_id = bands.band_id) order by
> showinfo.show_date"

Hello,

There is a problem with this structure that will keep you from being able to
return all this information in one *logical* query.

>From this structure, you have bands, shows that the band is in, and pictures
of the band.  The pictures are not really for any particular show.

Therefore, a single query to return all the information doesn't really apply
here.

If the pictures were for single shows, then you would need to add show_id to
the PHOTOS table, and then you could do something like this:

SELECT
  BANDS.band_name,
  SHOWINFO.show_date,
  PHOTOS.photo_name
FROM BANDS
INNER JOIN  SHOWINFO
  ON SHOWINFO.band_id = BANDS.band_id
INNER JOIN PHOTOS
  ON PHOTOS.band_id = BANDS.band_id
  AND PHOTOS.show_id = SHOWINFO.show_id
ORDER BY SHOWINFO.show_date

If a band didn't have photos for a particular show, but the band was in the
show, then the above query would return nothing.  If you want a record if
the band was in the show EVEN IF there are no available photos, then you
could try:

SELECT
  BANDS.band_name,
  SHOWINFO.show_date,
  PHOTOS.photo_name
FROM BANDS
INNER JOIN  SHOWINFO
  ON SHOWINFO.band_id = BANDS.band_id
LEFT OUTER JOIN PHOTOS
  ON PHOTOS.band_id = BANDS.band_id
  AND PHOTOS.show_id = SHOWINFO.show_id
ORDER BY SHOWINFO.show_date

Good luck,





More information about the thelist mailing list