Multi join query returns to many results and improperly matched
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)
For clarification the mapping of Cats goes like this:
The id of the Cat is stored in the Source table under the column catId. The Status table has a reference to the PK of Source as the column labeled catSource.
In practice to get the Status of the current cat the query would be:
select cat.* from cats cat, status status, source source where cat.id = source.catId and source.sourceId = status.catSource
select * from source inner join cats on source.catid = cats.id and source.sourceid = 1 left join status on source.sourceid = status.catsource
select * from source inner join cats on source.catid = cats.id left join status on source.sourceid = status.catsource and statusid=1
The expected data seems a little off. Check this query (using oracle syntax).
select c.name, c.age, c.length, s.*, src.* from cats c, status s, Source src where c.id = s.StatusId(+) and c.id = src.sourceId(+) order by c.name Adam 1 25 2 2 Bill 5 1 Charles 7 5 Steve 12 15 1 1 1 1 2 Steve 12 15 1 1 1 1 1