CODEWITHSUNDEEP
ms-accessms-access-2013
Makaira Murakami
Makaira Murakami

Reputation: 61

Basics: Query results not returning as expected

I have less than basic knowledge of MS Access, as I only need to use it to pull down information irregularly before using R to do the manipulation. As a result, I have no SQL coding knowledge - I just use the Access GUI.

My problem: When I create a query that includes multiple tables Access seems to exclude the results that don't have values in all of the tables.

Solution: I'm looking for a simple way, through the GUI, to tell Access to include all the IDs in the parent table, irrespective of whether they have values in any of the child tables. Those IDs that have no values in the child tables should just return with blanks in those columns.

I know this is probably SQL 101 but my searching hasn't returned anything useful.

Upvotes: 0

Views: 307

Answers (1)

Bruno Marco Visioli
Bruno Marco Visioli

Reputation: 418

You should use LEFT JOIN or RIGHT JOIN, the direction meaning the table from which you want to get all rows. See the select below:

SELECT * FROM TABLE_A a LEFT JOIN TABLE_B b ON a.id=b.id

This will return all rows from TABLE_A linked to the corresponding rows from TABLE_B. When there is no match the TABLE_B columns will return NULL.

Upvotes: 1

Related Questions