Retrieve all entries in table1 eventho they're not matched in tabl

[ali]

I have 2 tables.

-------------------------------------------------------
Table1 looks like this:
ID Name Department
101 Allen Accounting
102 Sarah Accounting
103 James HR
104 Nicole Finance
105 Claire IT
224 Kingston External
-----------------------------------------------------

Table 2:
ID Name Nationality
101 Allen USA
102 Sarah UK
103 James Canada
104 Nicole USA
105 Claire Germany
106 Steve Taiwan
107 Satoshi Japan

-----------------------------------------------------

Problem:

If i link them by "ID", i'll be only getting 5 entries 101, 102, 103, 104 &
105 from table 1. Because "Kingston 224" has no record in table2.
-----------------------------------------------------

Question:


If i want the query to return all entries in table1, doesn't matter if the
ID is found or not.

If the ID is not found, just return "blank" instead.

Could someone please help with this query ?

Dear experts, thanks a lot in advance ! ^_^

 

[Allen Browne]

Use an outer join.

Details in:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

 

[ali]

currently i'm using the following code:

SELECT Table1.ID, Table1.Name, Table2.Nation
FROM Table2 INNER JOIN Table1 ON Table2.ID = Table1.ID;

--------------------------------------------------------------------------------

Allen, will you show me the correct SQL for this query please !

Big thanks in advance !

 

[ali]

Hi Allen,

will you show me the code of "outer Join" please ?!

i'm using "Left Join" and "Right Join" , what's the difference between these
JOINS ?

Kindly let me know some of these knowledge!

Big thanks in advance !

 

[Allen Browne]

Did you check the article?

It suggested you double-click the line joining the 2 tables in the upper
pane of query design.

If you try the 3 options, and look at the SQL View, the lights will go on.

 

[ali]

Allen, thanks a lot!

Your article really helped me a lot !