Forum Stats

  • 3,750,549 Users
  • 2,250,192 Discussions
  • 7,867,004 Comments

Discussions

Query excludes unmatched counts

User_LGCCJ
User_LGCCJ Member Posts: 6 Employee

 A Table has to be joined in a view definition to add field.

Left outer join is being done in existing view with new table. But not getting the unmatched record from left table.

Even the right outer join and inner join is giving same no of records.

before change in view definition (count) : 9237566

after joining with new table:9096807

Suspect if in_id filter is negating the outer join somehow? Could not get other filter there which can filter out 140759 records.

sample query:

select

  p.*

  , ji.*

  , jit.name

  , jip.name

  , jis.name

  , jir.name

  , cf.st_pt --added new field

  , cf.ename --added new field

from

  pr   p

  , iss  ji

  , ity  jit

  , pty  jip

  , iss  jis

  , res  jir

  , cu_f  cf --added new table

where

  ji.pr = p.id

  and ji.isty = jit.id

  and ji.prty = jip.id (+)

  and ji.isst = jis.id

  and ji.res = jir.id (+)

  and ji.id = cf.is (+) --added condition

  and p.in_id = 1

  and ji.in_id = 1

  and jit.in_id = 1

  and jip.in_id = 1

  and jis.in_id = 1

  and jir.in_id = 1

  and cf.in_id = 1 --added condition

Best Answers

  • James Su
    James Su Member Posts: 1,114 Gold Trophy
    Accepted Answer

    change the last line to:

    and cf.in_id(+) = 1 --added condition

    otherwise it becomes an inner join.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,890 Red Diamond
    Accepted Answer

    Hi, @User_LGCCJ

    Here's a rule of thumb for outer joins:

    When using the old join notation, if a column from a table is marked with a (+) sign, as a column from cf is in

     and ji.id = cf.is (+) --added condition

    then all conditions involving that table must have a (+) sign somewhere, as in the suggestion by James

    and cf.in_id (+) = 1 --added condition


    However, I suggest you use ANSI join syntax for all joins, especially outer joins. Avoiding problems like this is just one of the reasons.

Answers

  • James Su
    James Su Member Posts: 1,114 Gold Trophy
    Accepted Answer

    change the last line to:

    and cf.in_id(+) = 1 --added condition

    otherwise it becomes an inner join.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,890 Red Diamond
    Accepted Answer

    Hi, @User_LGCCJ

    Here's a rule of thumb for outer joins:

    When using the old join notation, if a column from a table is marked with a (+) sign, as a column from cf is in

     and ji.id = cf.is (+) --added condition

    then all conditions involving that table must have a (+) sign somewhere, as in the suggestion by James

    and cf.in_id (+) = 1 --added condition


    However, I suggest you use ANSI join syntax for all joins, especially outer joins. Avoiding problems like this is just one of the reasons.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,890 Red Diamond

    Hi, @User_LGCCJ

    To use ANSI join notation, change the FROM and WHERE clauses to

    FROM      pr    p
    JOIN	  iss	ji  ON   ji.pr	   = p.id
    JOIN	  ity	jit ON	 jit.id	   = ji.isty
    LEFT JOIN pty	jip ON	 jip.id	   = ji.prty
       	 	    AND  jip.in_id = 1
    JOIN 	  iss	jis ON	 jis.id	   = ji.isst
    LEFT JOIN res	jir ON	 jir.id	   = ji.res
       	 	    AND  jir.in_id = 1
    LEFT JOIN cu_f	cf  ON	 cf.is	   = ji.id
       	 	    AND cf.in_id   = 1
    WHERE	 p.id      = 1
    AND	 ji.id	   = 1
    AND	 jit.in_id = 1
    AND	 jis_in_id = 1
    

    Notice that some of the conditions that were originally in the WHERE clause are now in ON clauses. You could put any or all of the remaining conditions in ON clauses as well, but most people find it clearer if conditions (other than join conditions) that only involve one table are in the WHERE clause.

    While it is possible to use both old-style join notation and ANSI join notation in the same query, it's a bad idea. It's hard to read and understand, which makes it hard to debug and maintain.

    By the way, since your table names are so short, do you really gain anything by using table aliases? For example, instead of making jit an alias for ity, why not simply reference ity everywhere you're currently referencing jit?