This content has been marked as final. Show 3 replies
What part of it doesn't work for you?
SQL> SELECT * FROM tablea;John
SQL> SELECT * FROM tableb;
SQL> CREATE VIEW view_test AS
2 SELECT a.subject, b.member
3 FROM tablea a
4 LEFT OUTER JOIN tableb b
5 ON (a.subject = b.subject);
SQL> SELECT *
2 FROM view_test
3 WHERE member = 'tim';
I see what's being asked for here - and why it won't work with the view.
You want to see all SUBJECTs and also match with the MEMBER name you're looking for against the subjects which appear in table B.
Your view is giving you the result of the outer join - by using "where member='tim'" you are seeing just the one row which has tim, i.e. "bio tim", because the other rows don't match (they eitehr have a member name in or is NULL)
select distinct(subject), decode(member,'TIM',member,null)
you could perform a decode to get what you want but you might just as well use the SQL statement you refer to in your original posting.