join... or other solution?

[bitsmith2k]

i'll give this a shot here.. its the end of the week and i'm not functioning on all cylinders here.. hope you guys can help.

my table structure is:
tblMain_Schedule:
schTime (DATETIME), Site (VARCHAR), Event (VARCHAR)

tblMain_TransTickets:
Site (VARCHAR), Event (VARCHAR), Date (DATETIME), Time(SMALLDATETIME), Quantity (INT)

as far as data i'll provide a simplified sample
tblMain_Schedule:
9:00 Aero Car General
9:12 Aero Car General
9:24 Aero Car General
9:36 Aero Car General
9:48 Aero Car General
10:00 Aero Car General

tblMain_TransTickets:
Aero Car General 05/22/2003 9:00 2
Aero Car General 05/22/2003 9:36 1

ok.. i want to build a view that would display information like this:
9:00 05/22/2003 Aero Car General 2
9:12 05/22/2003 Aero Car General 0
9:24 05/22/2003 Aero Car General 0
9:36 05/22/2003 Aero Car General 1
9:48 05/22/2003 Aero Car General 0
10:00 05/22/2003 Aero Car General 0


any ideas or suggestions??

thanks

mike

 

[swampBoogie]

select s.schtime,s.site, s.event, coalesce(quantity,0)
from tblMain_Schedule s left join tblMain_TransTickets t
 on s.schtime = t.date
 and s.site = t.site
 and s.event = t.event

Why do you have two datetime columns in tblMain_TransTickets? A datetime in SQL server always contains date and time so it seems redundant.

 

[bitsmith2k]

thanks swampBoogie..

this database has so many strange things in it that i've stopped trying to rationalize why they are there..
i guess when they wrote it they wanted to keep the date and time seperate.. i dunno why, because they have to be manually seperated anyway in the source code.

anyways..... i've been fiddling with your query.. it doesnt want to display the quantity for times that have it..
also how would i get it to output for multiple days?

thanks again for your help..

mike