[thelist] complicated sql

[Tomas Kokoska]

What about JOIN ?

SQLQuery = "SELECT s.show_date, b.band_name, p.photo_name
                                        FROM showinfo s INNER JOIN bands b
ON s.band_id = b.id
                                        LEFT JOIN photos p ON s.band_id =
p.photo_bandid
                                        ORDER BY s.show_date"

I have used LEFT JOIN to join photos table presuming you don't have to have
photo for each band.
Final result depends on data structure (have you just one photo for each
band (1:1) or can you have more photos or none?)
So, if that query written above wouldn't work how you would expected, try to
describe your situation more exactly.

Tom


> Hey everyone, I have a complicated SQL query but I can't produce the
results
> that I want...
>
> 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?  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"
>
> But that didn't quite work...
>
> Any ideas what I'm doing wrong?  I'd really appreciate the help.
>
> Thanks!
> --
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>