Merge 2 tables with conditions

Hi!

I have 2 tables:

Table Users:
ID
Name
Password
Birth

Table Status:
ID
Status

Both tables are related by "ID" field, but both tables have not the
same number of elements

I want to obtain a new table with this structure:

Table Result:
ID
Name
Password
Birth
Status

As I said before both tables have not the same number of elements
(rows) so in my result table, "Status" field should be empty for those
"ID"'s not found in "Status" table.

Example:

Users: 1 2 3
John Mary Peter
1234 3333 3456
5/11/81 9/3/80 7/7/69

Staus: 1 3
CODE1 CODE5

Result: 1 2 3
John Mary Peter
1234 3333 3456
5/11/81 9/3/80 7/7/69
CODE1 (EMPTY) CODE5

I am working with access, but I have knwoledge of SQL.

Thanks in advance.

Re: Merge 2 tables with conditions

On Tue, 06 Nov 2007 00:26:48 -0800, Vicente wrote:

You need an outer join.
Create new query > select both tables > draw a line between the two ID
fields if it's not created automatically > right-click the line >
Choose 'Include all from Users and only ...'

-Tom.


>Hi!
>
>I have 2 tables:
>
>Table Users:
> ID
> Name
> Password
> Birth
>
>Table Status:
> ID
> Status
>
>Both tables are related by "ID" field, but both tables have not the
>same number of elements
>
>I want to obtain a new table with this structure:
>
>Table Result:
> ID
> Name
> Password
> Birth
> Status
>
>As I said before both tables have not the same number of elements
>(rows) so in my result table, "Status" field should be empty for those
>"ID"'s not found in "Status" table.
>
>Example:
>
>Users: 1 2 3
> John Mary Peter
> 1234 3333 3456
> 5/11/81 9/3/80 7/7/69
>
>Staus: 1 3
> CODE1 CODE5
>
>Result: 1 2 3
> John Mary Peter
> 1234 3333 3456
> 5/11/81 9/3/80 7/7/69
> CODE1 (EMPTY) CODE5
>
>I am working with access, but I have knwoledge of SQL.
>
>Thanks in advance.

Re: Merge 2 tables with conditions

SELECT users.id, users.name, users.password, status.status, users.birth
FROM users LEFT JOIN status
ON users.id = status.id



In the query designer, you can switch from the SQL view to the graphical
view to see how your could have done it, graphically.


Hoping it may help,
Vanderghast, Access MVP


"Vicente" wrote in message
news:1194337608.699680.106990@d55g2000hsg.googlegroups.com.. .
> Hi!
>
> I have 2 tables:
>
> Table Users:
> ID
> Name
> Password
> Birth
>
> Table Status:
> ID
> Status
>
> Both tables are related by "ID" field, but both tables have not the
> same number of elements
>
> I want to obtain a new table with this structure:
>
> Table Result:
> ID
> Name
> Password
> Birth
> Status
>
> As I said before both tables have not the same number of elements
> (rows) so in my result table, "Status" field should be empty for those
> "ID"'s not found in "Status" table.
>
> Example:
>
> Users: 1 2 3
> John Mary Peter
> 1234 3333 3456
> 5/11/81 9/3/80 7/7/69
>
> Staus: 1 3
> CODE1 CODE5
>
> Result: 1 2 3
> John Mary Peter
> 1234 3333 3456
> 5/11/81 9/3/80 7/7/69
> CODE1 (EMPTY) CODE5
>
> I am working with access, but I have knwoledge of SQL.
>
> Thanks in advance.
>