inner join issue 1

[overDeveloper]

I am inner joining to a table that may or may not have corresponding records. For example, say I have the following tables

TableA
id col1
1 aa
2 bb
3 cc
4 dd
5 ee

TableB
id Aid col1
1 3 xx
2 4 yy

I want to do a join with b from a but I want to get ALL of A's records and add in B's values when they are there. When I do just a straight inner join I get only a.id = 3 and a.id = 4

 

[r937]

use a left outer join

r937.com | rudy.ca

 

[overDeveloper]

OK - that has partially worked - I am getting the null ones but the ones with records in tableB are repeated: So I am getting:

a.id=1
a.id=2
a.id=3
a.id=3
a.id=4
a.id=4
a.id=5

???

 

[overDeveloper]

let em correct that: if the tables look like -

TableA
id col1
1 aa
2 bb
3 cc
4 dd
5 ee

TableB
id Aid col1
1 3 xx
1 3 bb
2 4 yy

I get the result:

a.id=1
a.id=2
a.id=3
a.id=3
a.id=4
a.id=5

so it repeats for each record in b - I really only need the latest anyway.... I only need 1 for each in a but I need it whether it has a corresponding b record or not....

 

[r937]

getting the "latest" implies that you have some kind of dateadded column --

select a.id
     , a.col1
     , b.id
     , b.col1
     , b.dateadded
  from TableA as a
left outer
  join TableB as b
    on b.Aid = a.id
   and b.dateadded = 
       ( select max(dateadded)
           from TableB
          where Aid = a.id )

r937.com | rudy.ca