Multi join query returns to many results and improperly matched
- by Woot4Moo
I have the following minimal schema in Oracle: http://sqlfiddle.com/#!4/c1ed0/14
The queries I have run yield too many results and this query:
select cat.*, status.*, source.*
from cats cat, status status, source source
Left OUTER JOIN source source2
on source2.sourceid = 1
Right OUTER JOIN status status2
on status2.isStray =0
order by cat.name
will yield incorrect results. What I am expecting is a table that looks like the following however I cannot seem to come up with the correct SQL.
NAME AGE LENGTH STATUSID CATSOURCE ISSTRAY SOURCEID CATID
Adam 1 25 null null null 1 2
Bill 5 1 null null null null null
Charles 7 5 null null null null null
Steve 12 15 1 1 1 1 1
In plain English what I am looking for is to return all known cats + their associated cat source + their cat status while retaining null values. The only information I will have is the source that I am curious about. I also only want the cats that have a status of either STRAY or UNKNOWN (null)
