Joining two tables, one of which containing the other

I am new in the SQL world (my problem might be very easy to solve). Also,I work exclusively on Access.

I’m french and I realise that it is hard for me to explain so I’ll give you a demo after. I have two tables, A and B. Both have a column id and all of the id of B are in A (but B has different columns however) I’d like to join them, based on the id of course, to obtain a table containing A plus B when there is a matching id. Therefore result must have the exact number of rows as table A.

Table A
id - value1 - value2
1      0.5      0.6
2      0.4      0.9
3      0.4      1
4      0.8      1

Table B
id - name1 - name2
1     az      at
4     df      ml

Result:

id- value1- value2 - name1- name2
1    0.5     0.6       az    at
2    0.4     0.9
3    0.4      1    
4    0.8      1        df    ml

When an element of A is not in B, I still need to have them in the result, with a null value in the columns where the information is missing. If you don’t understand my english, just look at the demo, it’s self explanatory

I tried to code a FULL OUTER JOIN by myself (because it doesn’t exist in Access) but the result I get is a table with a lot of duplicates. I also tried a lot of JOINs but I always get a table with a lot more rows than A

Thanks in advance 🙂

Answer

I think you just need a left join here:

SELECT
    a.id,
    a.value1,
    a.value2,
    b.name1,
    b.name2
FROM TableA a
LEFT JOIN TableB b
    ON a.id = b.id;

A left join would guarantee that every row in TableA would appear in the result set, even if a given row’s id value did not match to anything in TableB.

If you don’t want NULL to possibly appear for the name1 and name2 fields for those records where there was no match, then consider using Nz(name1, '') to show empty string instead.

Advertisement

Answer