conditional 'where' statement question

[emblewembl]

I have a view which gives me a list of data - here's the code:

CREATE             view iVRunners

as

select 	r.racecardid, r.trackid, r.coursesurface,  e.entryid, e.programNo

from racecards r, schedules s, entries e

where r.racedate = s.mDate
and e.racecardid = r.racecardid

group by r.racecardid, e.entryid, r.trackid, r.coursesurface, e.programNo

I also want to pull in one more value (let's call it newVal) from another table, NewTable, but in NewTable there may only be a reference to 10 of the items currently being returned in my list. If I change the stored procedure to:

CREATE             view iVRunners

as

select 	r.racecardid, r.trackid, r.coursesurface,  e.entryid, e.programNo, o.newVal

from racecards r, schedules s, entries e, newTable o

where r.racedate = s.mDate
and e.racecardid = r.racecardid
and e.entryid = o.entryid

group by r.racecardid, e.entryid, r.trackid, r.coursesurface, e.programNo, o.newVal

I'll get only the runners back who have a value in NewTable. What I actually want is to get my original list of runners back with a value for NewVal if found in NewTable, otherwise a null.

How do I do this?


i love chocolate

 

[gmmastros]

You should use the new style joins. When you want all records from 1 table and matching records from another table, you need to use a left join. If there are no matching records, then all the fields will benull.

Also, you should only add a group by if you are using aggregate functions like SUM, MAX, Count, etc...

CREATE view iVRunners
as

select r.racecardid, 
       r.trackid, 
       r.coursesurface,  
       e.entryid, 
       e.programNo, 
       o.newVal
from   racecards r
       Inner Join schedules s
         On R.racedate = S.mDate
       Inner Join Entries E
         On e.racecardid = r.racecardid
       Left Join NewTable o
         On e.entryid = o.entryid

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[emblewembl]

Thanks, that worked great!

i love chocolate