Select not returning proper row
Posted on 2008-11-07
I have 2 tables
first table has primary key on id column and also has a name column.
second table has primary key on id. It also has date and subject columns.
Example of table 1 data:
123, 02032008, test1
123, 02042008, test1
123, 02052008, test1
223, 02062008, test1
333, 02062008, test1
I need to write a query that will return the name, date and subject.
That I have no problem doing.
However, I also need to return the name, even if there is no record in table 2 for the user and date provided.
I tried an outer join, a not exists, a not in, etc... and just can't seem to get it figured out.
Here is my select statement:
select distinct(name_name), subject_date, subject_subject from name, subject where name_id=subject_id(+) and name_id = 123 and (subject_date = 02062008 or name_id not in (select distinct(subject_id) from subject where subject_date = '02062008'));
What I get back is 3 rows:
jim, 02032008, test1
jim, 02042008, test1
jim, 02052008, test1
What I am looking to get back is 1 row with jim and no date or subject??