outer join query

• awp

  SSChasing Mays

  Points: 613

  More actions

  July 29, 2010 at 5:37 pm

  #103962

  table 1:

  personId (pk)

  forename

  surname

  table 2:

  ID

  personID (FK)

  data1_T2

  data2_T2

  table3:

  ID

  personId (FK)

  data1_T3

  data2_T3

  I want to joins table 2 and 3 using personId, if a matching found then I want all the data from both tables, if no matching found on table 2 then I still want all the data from table 3.

  I am using a outer join query as follows but it only yields when there is a matching in both tables, can you please help?

  select table2.data1, table2.data2, table3.data1, table3.data2 from

  table2 outer join table3 on table2.personID = table3.personId

  where (table2.personid = 200 or (table3.personId=200 and table2.personID is null))

• awp

  SSChasing Mays

  Points: 613

  More actions

  July 29, 2010 at 5:51 pm

  #1200087

  sorry made a mistake in the query, I am using FULL Join as follows:

  select table2.data1, table2.data2, table3.data1, table3.data2 from

  table2 FULL join table3 on table2.personID = table3.personId

  where (table2.personid = 200 or (table3.personId=200 and table2.personID is null))