Left Outer Join and setting default values for no rows

• bshulam

  SSC-Addicted

  Points: 406

  More actions

  January 17, 2008 at 4:16 pm

  #182815

  Long story, trying to fix some old code and sql that is way to complicated with views and views of views etc. the result of c2 was being set as null this RIGHT FUNCTIONS(c1, C2 - which is null) would blow up so I want to:

  Select t2.c1, t1.c2 from table2 t2 LEFT OUTER JOIN table1 t1 ON

  t2.c1 = t1.c1

  LEFT JOIN (Select 8 as c2 FROM table2, table1 WHERE table2.c1 != table1.c1) ON

  table2.c1 = table1.c1

  Goal.

  table 1 -< have this table

  c1 c2

  A 1

  C 2

  D 2

  G 4

  table 2 <-have this table

  c1

  A

  B

  C

  D

  E

  F

  G

  If no matching row in table 1 for table 2 then left join table 2 to table 1 so t1.c2 will = constant 8 for rows with not match on the join

  result of SP or view would be

  table 3 <- product of view or stored procedure?

  C1 C2

  A 1

  B 8

  C 2

  D 2

  E 8

  F 8

  G 4

• Chad Crawford

  SSChampion

  Points: 11610

  More actions

  January 17, 2008 at 4:33 pm

  #769496

  I'm not sure I completely understand, but I think this will give you the result you have in the last table:

  SELECT t2.c1, ISNULL(t1.c2, 8)

  FROM t2 LEFT JOIN t1

  ON t2.c1 = t1.c1

• bshulam

  SSC-Addicted

  Points: 406

  More actions

  January 17, 2008 at 5:15 pm

  #769503

  GREAT THAT WORKS.