Left join problems - returning all columns

Databases
1

given

table ‘ttimes’ with columns resno, vid, ttime
table ‘residents’ with columns lname, fname, vid

I want a result set that returns all columns from the ttimes table and all rows that match ttime.

I want the result set to show the residents fname and residents lname if there is a matching vid in both tables. If there is not a matching vid in the residents table I still want to show the columns from the ttimes table.

so, I say select * from ttimes left join residents on residents.vid = ttimes.vid where date(ttime) = ‘2010-11-06’

I get the result I want except the vid column displays nothing when there is no matching vid in the residents column.

I’m sure that this has something to do with ambiguous column names which I normally solve with as but I can’t figure this one out.

Any help is appreciated.

Mac

2

you tried a left outer join yet?

3

yeah, he did…

so, I say select * from ttimes left join residents on residents.vid = ttimes.vid where date(ttime) = ‘2010-11-06’

the problem is that he’s using the dreaded, evil “select star”

fix that and the problem goes away

:slight_smile: