|
 |
access thread: Simulating Full Join in Access
Message #1 by georgebiko@h... on Thu, 21 Nov 2002 17:30:51
|
|
Hi,
I'm trying to do a FULL JOIN as in SQL Server ie. return records from two
tables wether they match or not. Access don't have FULL JOIN. How do I go
about it.
Thanks
Biko
Message #2 by "Carnley, Dave" <dcarnley@a...> on Thu, 21 Nov 2002 11:43:38 -0600
|
|
I think if you add two (or more) tables to a QBE window, then select fields
to display, without making any links between the tables, you get what you
are looking for. Another name for this is Cross Product or Cartesian
product. (is that correct, Leo??) :)
-----Original Message-----
From: georgebiko@h... [mailto:georgebiko@h...]
Sent: Thursday, November 21, 2002 11:31 AM
To: Access
Subject: [access] Simulating Full Join in Access
Hi,
I'm trying to do a FULL JOIN as in SQL Server ie. return records from two
tables wether they match or not. Access don't have FULL JOIN. How do I go
about it.
Thanks
Biko
Message #3 by "Bob Bedell" <bobbedell15@m...> on Thu, 21 Nov 2002 17:43:46 +0000
|
|
Use a union query,
SELECT
tlkpCompanyType.CompanyTypeID,
tlkpCompanyType.CTDescription,
tblCustomer.CustomerID,
tblCustomer.CustomerName
FROM
tlkpCompanyType
LEFT JOIN tblCustomer
ON tlkpCompanyType.CompanyTypeID = tblCustomer.CompanyTypeID
UNION SELECT
tlkpCompanyType.CompanyTypeID,
tlkpCompanyType.CTDescription,
tblCustomer.CustomerID,
tblCustomer.CustomerName
FROM
tlkpCompanyType
RIGHT JOIN tblCustomer
ON tlkpCompanyType.CompanyTypeID = tblCustomer.CompanyTypeID;
>From: georgebiko@h...
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Simulating Full Join in Access
>Date: Thu, 21 Nov 2002 17:30:51
>
>Hi,
>I'm trying to do a FULL JOIN as in SQL Server ie. return records from two
>tables wether they match or not. Access don't have FULL JOIN. How do I go
>about it.
>Thanks
>Biko
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus
Message #4 by "Leo Scott" <leoscott@c...> on Thu, 21 Nov 2002 10:08:29 -0800
|
|
Ok, I would expect a cartesian product unless you use SELECT DISTINCT.
|-----Original Message-----
|From: Carnley, Dave [mailto:dcarnley@a...]
|Sent: Thursday, November 21, 2002 9:44 AM
|To: Access
|Subject: [access] RE: Simulating Full Join in Access
|
|
|I think if you add two (or more) tables to a QBE window, then select fields
|to display, without making any links between the tables, you get what you
|are looking for. Another name for this is Cross Product or Cartesian
|product. (is that correct, Leo??) :)
|
|-----Original Message-----
|From: georgebiko@h... [mailto:georgebiko@h...]
|Sent: Thursday, November 21, 2002 11:31 AM
|To: Access
|Subject: [access] Simulating Full Join in Access
|
|
|Hi,
|I'm trying to do a FULL JOIN as in SQL Server ie. return records from two
|tables wether they match or not. Access don't have FULL JOIN. How do I go
|about it.
|Thanks
|Biko
|
|
Message #5 by "Bob Bedell" <bobbedell15@m...> on Thu, 21 Nov 2002 18:32:41 +0000
|
|
Hi guys,
A full join isn't a cartesian product. It simply returns all row from
both tables whether or not they match; it doesn't display each row in
table A for each row in table B, as a cartesian product does. The result
set for the union query below looks like the following:
I hope this formats OK:
CompanyTypeID CTDescription CustomerID CustomerName
5 Building Management
1 Software Developmet 2 Synthesystems
2 Manufacturing 1 ABC Inc.
3 GIS
4 Network Support
5 Retail 4 Acme Roadrunner
6 Consulting 3 PH&V
That sort of thing, all records from both tables, regardless of matches.
Full joins union a left join and a right join between the same two
tables.
Best,
Bob
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Simulating Full Join in Access
>Date: Thu, 21 Nov 2002 17:43:46 +0000
>
>Use a union query,
>
>SELECT
> tlkpCompanyType.CompanyTypeID,
> tlkpCompanyType.CTDescription,
> tblCustomer.CustomerID,
> tblCustomer.CustomerName
>FROM
> tlkpCompanyType
> LEFT JOIN tblCustomer
> ON tlkpCompanyType.CompanyTypeID = tblCustomer.CompanyTypeID
>UNION SELECT
> tlkpCompanyType.CompanyTypeID,
> tlkpCompanyType.CTDescription,
> tblCustomer.CustomerID,
> tblCustomer.CustomerName
>FROM
> tlkpCompanyType
> RIGHT JOIN tblCustomer
> ON tlkpCompanyType.CompanyTypeID = tblCustomer.CompanyTypeID;
>
>>From: georgebiko@h...
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Simulating Full Join in Access
>>Date: Thu, 21 Nov 2002 17:30:51
>>
>>Hi,
>>I'm trying to do a FULL JOIN as in SQL Server ie. return records from two
>>tables wether they match or not. Access don't have FULL JOIN. How do I go
>>about it.
>>Thanks
>>Biko
>
>
>_________________________________________________________________
>MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
>http://join.msn.com/?page=features/virus
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
Message #6 by "Bob Bedell" <bobbedell15@m...> on Thu, 21 Nov 2002 18:42:02 +0000
|
|
I guess to put it another way, a full join is a type of join between
two tables, whereas a Cartesian product results from the absence of a
join between two tables.
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Simulating Full Join in Access
>Date: Thu, 21 Nov 2002 18:32:41 +0000
>
>Hi guys,
>
>A full join isn't a cartesian product. It simply returns all row from
>both tables whether or not they match; it doesn't display each row in
>table A for each row in table B, as a cartesian product does. The result
>set for the union query below looks like the following:
>
>I hope this formats OK:
>
>CompanyTypeID CTDescription CustomerID CustomerName
> 5 Building Management
>1 Software Developmet 2 Synthesystems
>2 Manufacturing 1 ABC Inc.
>3 GIS
>4 Network Support
>5 Retail 4 Acme Roadrunner
>6 Consulting 3 PH&V
>
>That sort of thing, all records from both tables, regardless of matches.
>Full joins union a left join and a right join between the same two
>tables.
>
>Best,
>
>Bob
>
>>From: "Bob Bedell" <bobbedell15@m...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Re: Simulating Full Join in Access
>>Date: Thu, 21 Nov 2002 17:43:46 +0000
>>
>>Use a union query,
>>
>>SELECT
>> tlkpCompanyType.CompanyTypeID,
>> tlkpCompanyType.CTDescription,
>> tblCustomer.CustomerID,
>> tblCustomer.CustomerName
>>FROM
>> tlkpCompanyType
>> LEFT JOIN tblCustomer
>> ON tlkpCompanyType.CompanyTypeID = tblCustomer.CompanyTypeID
>>UNION SELECT
>> tlkpCompanyType.CompanyTypeID,
>> tlkpCompanyType.CTDescription,
>> tblCustomer.CustomerID,
>> tblCustomer.CustomerName
>>FROM
>> tlkpCompanyType
>> RIGHT JOIN tblCustomer
>> ON tlkpCompanyType.CompanyTypeID = tblCustomer.CompanyTypeID;
>>
>>>From: georgebiko@h...
>>>Reply-To: "Access" <access@p...>
>>>To: "Access" <access@p...>
>>>Subject: [access] Simulating Full Join in Access
>>>Date: Thu, 21 Nov 2002 17:30:51
>>>
>>>Hi,
>>>I'm trying to do a FULL JOIN as in SQL Server ie. return records from two
>>>tables wether they match or not. Access don't have FULL JOIN. How do I go
>>>about it.
>>>Thanks
>>>Biko
>>
>>
>>_________________________________________________________________
>>MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
>>http://join.msn.com/?page=features/virus
>>
>>
>>---
>>Change your mail options at http://p2p.wrox.com/manager.asp or to
>>unsubscribe send a blank email to
>
>
>_________________________________________________________________
>Help STOP SPAM with the new MSN 8 and get 2 months FREE*
>http://join.msn.com/?page=features/junkmail
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
Message #7 by "Carnley, Dave" <dcarnley@a...> on Thu, 21 Nov 2002 13:29:04 -0600
|
|
Oh, sure, one of those ;)
Oracle 9i support the syntax SELECT T1.*, T2.* FROM T1 FULL JOIN T2 ON ...
but I don't think Access does
-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Thursday, November 21, 2002 12:42 PM
To: Access
Subject: [access] Re: Simulating Full Join in Access
I guess to put it another way, a full join is a type of join between
two tables, whereas a Cartesian product results from the absence of a
join between two tables.
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Simulating Full Join in Access
>Date: Thu, 21 Nov 2002 18:32:41 +0000
>
>Hi guys,
>
>A full join isn't a cartesian product. It simply returns all row from
>both tables whether or not they match; it doesn't display each row in
>table A for each row in table B, as a cartesian product does. The result
>set for the union query below looks like the following:
>
>I hope this formats OK:
>
>CompanyTypeID CTDescription CustomerID CustomerName
> 5 Building Management
>1 Software Developmet 2 Synthesystems
>2 Manufacturing 1 ABC Inc.
>3 GIS
>4 Network Support
>5 Retail 4 Acme Roadrunner
>6 Consulting 3 PH&V
>
>That sort of thing, all records from both tables, regardless of matches.
>Full joins union a left join and a right join between the same two
>tables.
>
>Best,
>
>Bob
>
>>From: "Bob Bedell" <bobbedell15@m...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Re: Simulating Full Join in Access
>>Date: Thu, 21 Nov 2002 17:43:46 +0000
>>
>>Use a union query,
>>
>>SELECT
>> tlkpCompanyType.CompanyTypeID,
>> tlkpCompanyType.CTDescription,
>> tblCustomer.CustomerID,
>> tblCustomer.CustomerName
>>FROM
>> tlkpCompanyType
>> LEFT JOIN tblCustomer
>> ON tlkpCompanyType.CompanyTypeID = tblCustomer.CompanyTypeID
>>UNION SELECT
>> tlkpCompanyType.CompanyTypeID,
>> tlkpCompanyType.CTDescription,
>> tblCustomer.CustomerID,
>> tblCustomer.CustomerName
>>FROM
>> tlkpCompanyType
>> RIGHT JOIN tblCustomer
>> ON tlkpCompanyType.CompanyTypeID = tblCustomer.CompanyTypeID;
>>
>>>From: georgebiko@h...
>>>Reply-To: "Access" <access@p...>
>>>To: "Access" <access@p...>
>>>Subject: [access] Simulating Full Join in Access
>>>Date: Thu, 21 Nov 2002 17:30:51
>>>
>>>Hi,
>>>I'm trying to do a FULL JOIN as in SQL Server ie. return records from two
>>>tables wether they match or not. Access don't have FULL JOIN. How do I go
>>>about it.
>>>Thanks
>>>Biko
>>
>>
>>_________________________________________________________________
>>MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
>>http://join.msn.com/?page=features/virus
>>
>>
>>---
>>Change your mail options at http://p2p.wrox.com/manager.asp or to
>>unsubscribe send a blank email to
>
>
>_________________________________________________________________
>Help STOP SPAM with the new MSN 8 and get 2 months FREE*
>http://join.msn.com/?page=features/junkmail
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
Message #8 by "Gerald, Rand" <RGerald@u...> on Thu, 21 Nov 2002 15:17:02 -0600
|
|
You can't do a FULL JOIN in Access, but you can do a UNION of a LEFT
JOIN
and a RIGHT JOIN.
I know, I just did one yesterday.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Thursday, November 21, 2002 13:29
To: Access
Subject: [access] Re: Simulating Full Join in Access
Oh, sure, one of those ;)
Oracle 9i support the syntax SELECT T1.*, T2.* FROM T1 FULL JOIN T2 ON
...
but I don't think Access does
-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Thursday, November 21, 2002 12:42 PM
To: Access
Subject: [access] Re: Simulating Full Join in Access
I guess to put it another way, a full join is a type of join between
two tables, whereas a Cartesian product results from the absence of a
join between two tables.
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Simulating Full Join in Access
>Date: Thu, 21 Nov 2002 18:32:41 +0000
>
>Hi guys,
>
>A full join isn't a cartesian product. It simply returns all row from
>both tables whether or not they match; it doesn't display each row in
>table A for each row in table B, as a cartesian product does. The
result
>set for the union query below looks like the following:
>
>I hope this formats OK:
>
>CompanyTypeID CTDescription CustomerID CustomerName
> 5 Building
Management
>1 Software Developmet 2 Synthesystems
>2 Manufacturing 1 ABC Inc.
>3 GIS
>4 Network Support
>5 Retail 4 Acme Roadrunner
>6 Consulting 3 PH&V
>
>That sort of thing, all records from both tables, regardless of
matches.
>Full joins union a left join and a right join between the same two
>tables.
>
>Best,
>
>Bob
>
>>From: "Bob Bedell" <bobbedell15@m...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Re: Simulating Full Join in Access
>>Date: Thu, 21 Nov 2002 17:43:46 +0000
>>
>>Use a union query,
>>
>>SELECT
>> tlkpCompanyType.CompanyTypeID,
>> tlkpCompanyType.CTDescription,
>> tblCustomer.CustomerID,
>> tblCustomer.CustomerName
>>FROM
>> tlkpCompanyType
>> LEFT JOIN tblCustomer
>> ON tlkpCompanyType.CompanyTypeID =3D tblCustomer.CompanyTypeID
>>UNION SELECT
>> tlkpCompanyType.CompanyTypeID,
>> tlkpCompanyType.CTDescription,
>> tblCustomer.CustomerID,
>> tblCustomer.CustomerName
>>FROM
>> tlkpCompanyType
>> RIGHT JOIN tblCustomer
>> ON tlkpCompanyType.CompanyTypeID =3D tblCustomer.CompanyTypeID;
>>
>>>From: georgebiko@h...
>>>Reply-To: "Access" <access@p...>
>>>To: "Access" <access@p...>
>>>Subject: [access] Simulating Full Join in Access
>>>Date: Thu, 21 Nov 2002 17:30:51
>>>
>>>Hi,
>>>I'm trying to do a FULL JOIN as in SQL Server ie. return records
from two
>>>tables wether they match or not. Access don't have FULL JOIN. How do
I go
>>>about it.
>>>Thanks
>>>Biko
>>
>>
>>_________________________________________________________________
>>MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
>>http://join.msn.com/?page=3Dfeatures/virus
>>
>>
>>---
>>Change your mail options at http://p2p.wrox.com/manager.asp or to
>>unsubscribe send a blank email to
>
>
>_________________________________________________________________
>Help STOP SPAM with the new MSN 8 and get 2 months FREE*
>http://join.msn.com/?page=3Dfeatures/junkmail
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=3Dfeatures/junkmail
|
|
|
View Cart
