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)

UPDATE

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

FINAL QUERY

select *
from source
    inner join cats on source.catid = cats.id
and source.sourceid = 1
    left join status on source.sourceid = status.catsource

Answers


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

Need Your Help


About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.