NYCPHP Meetup

[Dan Cech]

harvey wrote:
> Hello,
> 
> I'm drawing a blank on this...
> 
> I've got a table of "show" information and a table of month names.
> When listing the shows, dates, prices, etc, I do something like the 
> following:
> 
> SELECT shows.name, shows.id, month.month_name AS openmonth
> FROM shows, months
> WHERE shows.openmonth_fid = months.month_id
> 
> I get the opening month name fine.
> Now I also want to get the closing month name.
> But I know a query like the following won't work.
> 
> SELECT shows.name, shows.id, month.month_name AS openmonth, 
> month.month_name AS closemonth
> FROM shows, months
> WHERE shows.openmonth_fid = months.month_id
> AND shows.closemonth_fid = months.month_id
> 
> How would I go about doing something like the above?

You need the following syntax:

SELECT s.name,s.id,m1.month_name AS open,m2.month_name AS close
FROM shows AS s
JOIN months AS m1 ON m1.month_id = s.openmonth_id
JOIN months AS m2 ON m2.month_id = s.closemonth_id

Dan

> I know I can create two separate months tables (one for opens, one for 
> closes) but there's gotta be a better way, right?
> 
> Thanks!
> 
> Harvey