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

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 Phailat Wongakanit
0
Utf
1/10/2008 5:11:01 AM
access.queries 6343 articles. 1 followers. Follow

5 Replies
612 Views

Similar Articles

[PageSpeed] 3

Use an outer join.

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

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ali" <ali@discussions.microsoft.com> wrote in message
news:D1753457-1834-4720-BEEB-616D2DE18AC6@microsoft.com...
>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. 

0
Allen
1/10/2008 5:31:30 AM
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 !



-- 
Allen Phailat Wongakanit


"Allen Browne" wrote:

> Use an outer join.
> 
> Details in:
>     The Query Lost My Records!
> at:
>     http://allenbrowne.com/casu-02.html
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "ali" <ali@discussions.microsoft.com> wrote in message
> news:D1753457-1834-4720-BEEB-616D2DE18AC6@microsoft.com...
> >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. 
> 
> 
0
Utf
1/10/2008 7:01:00 AM
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 Phailat Wongakanit


"Allen Browne" wrote:

> Use an outer join.
> 
> Details in:
>     The Query Lost My Records!
> at:
>     http://allenbrowne.com/casu-02.html
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "ali" <ali@discussions.microsoft.com> wrote in message
> news:D1753457-1834-4720-BEEB-616D2DE18AC6@microsoft.com...
> >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. 
> 
> 
0
Utf
1/10/2008 7:06:00 AM
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.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ali" <ali@discussions.microsoft.com> wrote in message
news:4005DBD9-B5E8-4CE5-85D8-37B18DAD0BDD@microsoft.com...
> 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 Phailat Wongakanit
>
>
> "Allen Browne" wrote:
>
>> Use an outer join.
>>
>> Details in:
>>     The Query Lost My Records!
>> at:
>>     http://allenbrowne.com/casu-02.html
>>
>> "ali" <ali@discussions.microsoft.com> wrote in message
>> news:D1753457-1834-4720-BEEB-616D2DE18AC6@microsoft.com...
>> >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. 

0
Allen
1/10/2008 8:08:00 AM
Allen, thanks a lot!

Your article really helped me a lot ! 

-- 
Allen Phailat Wongakanit


"Allen Browne" wrote:

> 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.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "ali" <ali@discussions.microsoft.com> wrote in message
> news:4005DBD9-B5E8-4CE5-85D8-37B18DAD0BDD@microsoft.com...
> > 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 Phailat Wongakanit
> >
> >
> > "Allen Browne" wrote:
> >
> >> Use an outer join.
> >>
> >> Details in:
> >>     The Query Lost My Records!
> >> at:
> >>     http://allenbrowne.com/casu-02.html
> >>
> >> "ali" <ali@discussions.microsoft.com> wrote in message
> >> news:D1753457-1834-4720-BEEB-616D2DE18AC6@microsoft.com...
> >> >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. 
> 
> 
0
Utf
1/10/2008 8:36:03 AM
Reply:

Similar Artilces: