FULL OUTER JOIN ON 3 TABLES

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 tables with the following fields:

Tbl531:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblPhone:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblException:
SCHEDULE_START_DATE
RACF
Exception_Time: DateDiff('n',[start_time],[end_time])/60

What I would like to accomplish is a full-outer join of these three tables.
I want all records in the 531, phone and Exception tables to be matched on
the Date and RACF fields unless no match. There should be no days in the
phone table which do not exist in the 531 table, however, it is very possible
to have no data or multiple records available on a given day in the exception
table.

I have tried doing a left join on the 531 and Phone tables, and then a right
join on the resulting data set and the exception table. Not working so
well.....

Please help at a loss.
 
KJ-clueless said:
I have 3 tables with the following fields:

Tbl531:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblPhone:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblException:
SCHEDULE_START_DATE
RACF
Exception_Time: DateDiff('n',[start_time],[end_time])/60

What I would like to accomplish is a full-outer join of these three tables.
I want all records in the 531, phone and Exception tables to be matched on
the Date and RACF fields unless no match. There should be no days in the
phone table which do not exist in the 531 table, however, it is very possible
to have no data or multiple records available on a given day in the exception
table.

I have tried doing a left join on the 531 and Phone tables, and then a right
join on the resulting data set and the exception table. Not working so
well.....
Click to expand...


A full outer join has this kind of structure:

SELECT Q.ACTIVITYDATE, Q,RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q LEFT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF
UNION
SELECT T.ACTIVITYDATE, T.RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q RIGHT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF

where qry531xPhone is your Left Join query.
 
Marshall, thanks very much for your help. Just what I needed... working
perfectly now.

Marshall Barton said:
KJ-clueless said:
I have 3 tables with the following fields:

Tbl531:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblPhone:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblException:
SCHEDULE_START_DATE
RACF
Exception_Time: DateDiff('n',[start_time],[end_time])/60

What I would like to accomplish is a full-outer join of these three tables.
I want all records in the 531, phone and Exception tables to be matched on
the Date and RACF fields unless no match. There should be no days in the
phone table which do not exist in the 531 table, however, it is very possible
to have no data or multiple records available on a given day in the exception
table.

I have tried doing a left join on the 531 and Phone tables, and then a right
join on the resulting data set and the exception table. Not working so
well.....
Click to expand...


A full outer join has this kind of structure:

SELECT Q.ACTIVITYDATE, Q,RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q LEFT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF
UNION
SELECT T.ACTIVITYDATE, T.RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q RIGHT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF

where qry531xPhone is your Left Join query.
Click to expand...
 
Marshall Barton said:
KJ-clueless said:
I have 3 tables with the following fields:

Tbl531:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblPhone:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblException:
SCHEDULE_START_DATE
RACF
Exception_Time: DateDiff('n',[start_time],[end_time])/60

What I would like to accomplish is a full-outer join of these three
tables.
I want all records in the 531, phone and Exception tables to be matched on
the Date and RACF fields unless no match. There should be no days in the
phone table which do not exist in the 531 table, however, it is very
possible
to have no data or multiple records available on a given day in the
exception
table.

I have tried doing a left join on the 531 and Phone tables, and then a
right
join on the resulting data set and the exception table. Not working so
well.....
Click to expand...


A full outer join has this kind of structure:

SELECT Q.ACTIVITYDATE, Q,RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q LEFT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF
UNION
SELECT T.ACTIVITYDATE, T.RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q RIGHT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF

where qry531xPhone is your Left Join query.
Click to expand...
 
In some cases, you may wish to add


WHERE Q.ACTIVITYDATE IS NULL

at the end, and use UNION ALL rather than UNION. Indeed, it the join creates
duplicated row, a standard UNION will (erroneously) remove them (by
comparison to a real full outer join), but doing so, the UNION ALL would add
irrelevant records without the extra WHERE condition.


Sure, if there is NO DUPLICATED row, based on the ON criteria, which is
probably the case, you don't need to modify anything.



Vanderghast, Access MVP


Marshall Barton said:
KJ-clueless said:
I have 3 tables with the following fields:

Tbl531:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblPhone:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblException:
SCHEDULE_START_DATE
RACF
Exception_Time: DateDiff('n',[start_time],[end_time])/60

What I would like to accomplish is a full-outer join of these three
tables.
I want all records in the 531, phone and Exception tables to be matched on
the Date and RACF fields unless no match. There should be no days in the
phone table which do not exist in the 531 table, however, it is very
possible
to have no data or multiple records available on a given day in the
exception
table.

I have tried doing a left join on the 531 and Phone tables, and then a
right
join on the resulting data set and the exception table. Not working so
well.....
Click to expand...


A full outer join has this kind of structure:

SELECT Q.ACTIVITYDATE, Q,RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q LEFT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF
UNION
SELECT T.ACTIVITYDATE, T.RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q RIGHT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF

where qry531xPhone is your Left Join query.
Click to expand...
 
Excellent points, Vanderghast.
--
Marsh


Michel said:
In some cases, you may wish to add


WHERE Q.ACTIVITYDATE IS NULL

at the end, and use UNION ALL rather than UNION. Indeed, it the join creates
duplicated row, a standard UNION will (erroneously) remove them (by
comparison to a real full outer join), but doing so, the UNION ALL would add
irrelevant records without the extra WHERE condition.


Sure, if there is NO DUPLICATED row, based on the ON criteria, which is
probably the case, you don't need to modify anything.



Vanderghast, Access MVP


KJ-clueless said:
I have 3 tables with the following fields:

Tbl531:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblPhone:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblException:
SCHEDULE_START_DATE
RACF
Exception_Time: DateDiff('n',[start_time],[end_time])/60

What I would like to accomplish is a full-outer join of these three
tables.
I want all records in the 531, phone and Exception tables to be matched on
the Date and RACF fields unless no match. There should be no days in the
phone table which do not exist in the 531 table, however, it is very
possible
to have no data or multiple records available on a given day in the
exception
table.

I have tried doing a left join on the 531 and Phone tables, and then a
right
join on the resulting data set and the exception table. Not working so
well.....
Click to expand...


A full outer join has this kind of structure:

SELECT Q.ACTIVITYDATE, Q,RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q LEFT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF
UNION
SELECT T.ACTIVITYDATE, T.RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q RIGHT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF

where qry531xPhone is your Left Join query.
Click to expand...
Click to expand...
 
Hi,

I have 3 tables with a many-to-many relationship:

Table A
A_ID
A_Name

Table A_B
A_ID
C_ID

Table C
C_ID
C_NAME

I'd like to have a query that lists all records of A_Name (Table A) regardless it has C_Name or not. How do I do it in Access 2007?

I'll be very appreciated if someone could help me!
I have 3 tables with the following fields:

Tbl531:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblPhone:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblException:
SCHEDULE_START_DATE
RACF
Exception_Time: DateDiff('n',[start_time],[end_time])/60

What I would like to accomplish is a full-outer join of these three tables.
I want all records in the 531, phone and Exception tables to be matched on
the Date and RACF fields unless no match. There should be no days in the
phone table which do not exist in the 531 table, however, it is very possible
to have no data or multiple records available on a given day in the exception
table.

I have tried doing a left join on the 531 and Phone tables, and then a right
join on the resulting data set and the exception table. Not working so
well.....

Please help at a loss.
A full outer join has this kind of structure:

SELECT Q.ACTIVITYDATE, Q,RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q LEFT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF
UNION
SELECT T.ACTIVITYDATE, T.RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q RIGHT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF

where qry531xPhone is your Left Join query.
Click to expand...
Click to expand...
 
Robert said:
Hi,

I have 3 tables with a many-to-many relationship:

Table A
A_ID
A_Name

Table A_B
A_ID
C_ID

Table C
C_ID
C_NAME

I'd like to have a query that lists all records of A_Name (Table A)
regardless it has C_Name or not. How do I do it in Access 2007?

I'll be very appreciated if someone could help me!
Click to expand...
Surely this is as simple as:

Select A_ID,A_Name from [Table A]










Oh! You want it to list information from Table C if it exists? No
problem. It's a simple LEFT join (not sure why you thought this old
topic on Full Outer Joins was relevant ... ):
Select a.A_ID,A_Name, c.C_ID,C_Name
from ([Table A] As a left join [Table A_B] As b
ON a.A_ID = b.A_ID) left join [Table C]
ON b.C_ID=c.C_ID
 
That is not a full outer join. A full outer join lists all records in table A
and any associated values in table C PLUS all records in Table C with no
records in Table A.

PERHAPS what you want is to list all records in table A and any matching
values in table C (an outer join). That would look like the following.

SELECT A.*, C.*
FROM [A] LEFT JOIN [A_B]
ON A.A_ID = A_B.A_ID
LEFT JOIN C
ON A_B.C_ID = C.C_ID

If you want a FULL OUTER JOIN in Access you have to use a UNION query.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Robert said:
Hi,

I have 3 tables with a many-to-many relationship:

Table A
A_ID
A_Name

Table A_B
A_ID
C_ID

Table C
C_ID
C_NAME

I'd like to have a query that lists all records of A_Name (Table A) regardless it has C_Name or not. How do I do it in Access 2007?

I'll be very appreciated if someone could help me!
I have 3 tables with the following fields:

Tbl531:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblPhone:
ACTIVITYDATE
RACF
DAILYHOURSWORKED

TblException:
SCHEDULE_START_DATE
RACF
Exception_Time: DateDiff('n',[start_time],[end_time])/60

What I would like to accomplish is a full-outer join of these three tables.
I want all records in the 531, phone and Exception tables to be matched on
the Date and RACF fields unless no match. There should be no days in the
phone table which do not exist in the 531 table, however, it is very possible
to have no data or multiple records available on a given day in the exception
table.

I have tried doing a left join on the 531 and Phone tables, and then a right
join on the resulting data set and the exception table. Not working so
well.....

Please help at a loss.
A full outer join has this kind of structure:

SELECT Q.ACTIVITYDATE, Q,RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q LEFT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF
UNION
SELECT T.ACTIVITYDATE, T.RACF,
Q.DAILYHOURSWORKED,
T.Exception_Time
FROM qry531xPhone As Q RIGHT JOIN TblException As T
ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
AND Q.RACF = T.RACF

where qry531xPhone is your Left Join query.
Click to expand...
working
perfectly now.

"Marshall Barton" wrote:
Click to expand...
On Monday, July 16, 2007 4:24 PM Michel Walsh wrote:
In some cases, you may wish to add


WHERE Q.ACTIVITYDATE IS NULL

at the end, and use UNION ALL rather than UNION. Indeed, it the join creates
duplicated row, a standard UNION will (erroneously) remove them (by
comparison to a real full outer join), but doing so, the UNION ALL would add
irrelevant records without the extra WHERE condition.


Sure, if there is NO DUPLICATED row, based on the ON criteria, which is
probably the case, you don't need to modify anything.



Vanderghast, Access MVP


news:[email protected]...
Click to expand...
On Monday, July 16, 2007 5:47 PM Marshall Barton wrote:
Excellent points, Vanderghast.
--
Marsh


Michel Walsh wrote:
Click to expand...
Submitted via EggHeadCafe - Software Developer Portal of Choice
Silverlight Binary Serialization and Compression with WCF Services
http://www.eggheadcafe.com/tutorial...zation-and-compression-with-wcf-services.aspx
Click to expand...
Click to expand...
Click to expand...
 
Michel Walsh said:
"Marshall Barton" <[email protected]> wrote in message
news:[email protected]...
> KJ-clueless wrote:
>
>>I have 3 tables with the following fields:
>>
>>Tbl531:
>>ACTIVITYDATE
>>RACF
>>DAILYHOURSWORKED
>>
>>TblPhone:
>>ACTIVITYDATE
>>RACF
>>DAILYHOURSWORKED
>>
>>TblException:
>>SCHEDULE_START_DATE
>>RACF
>>Exception_Time: DateDiff('n',[start_time],[end_time])/60
>>
>>What I would like to accomplish is a full-outer join of these three
>>tables.
>>I want all records in the 531, phone and Exception tables to be matched on
>>the Date and RACF fields unless no match. There should be no days in the
>>phone table which do not exist in the 531 table, however, it is very
>>possible
>>to have no data or multiple records available on a given day in the
>>exception
>>table.
>>
>>I have tried doing a left join on the 531 and Phone tables, and then a
>>right
>>join on the resulting data set and the exception table. Not working so
>>well.....
>
>
> A full outer join has this kind of structure:
>
> SELECT Q.ACTIVITYDATE, Q,RACF,
> Q.DAILYHOURSWORKED,
> T.Exception_Time
> FROM qry531xPhone As Q LEFT JOIN TblException As T
> ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
> AND Q.RACF = T.RACF
> UNION
> SELECT T.ACTIVITYDATE, T.RACF,
> Q.DAILYHOURSWORKED,
> T.Exception_Time
> FROM qry531xPhone As Q RIGHT JOIN TblException As T
> ON Q.ACTIVITYDATE = T.SCHEDULE_START_DATE
> AND Q.RACF = T.RACF
>
> where qry531xPhone is your Left Join query.
>
> --
> Marsh
> MVP [MS Access]
Click to expand...


hi
if we want to put full outer join on three table then we can choose this approach
there is three table

SELECT
rec98.tax_amount01 tax_amount,rec98.tax_currency
,rec061.airline,rec061.ticket_number
,rec061.coupon,rec061.amount,rec061.currency
,rec061.isc_amount,rec061.currency isc_currency
,rec061.isc_percent
FROM

(SELECT
rec06.airline,rec06.ticket_number
,rec06.coupon,rec06.provs amount,rec06.cutps currency
,rec97.isc_amount,rec97.currency isc_currency
,rec97.isc_percent
FROM
ml_fnf_record06 rec06 FULL OUTER JOIN ml_fnf_record97 rec97
ON rec97.airline=rec06.airline and
rec97.ticket_number =rec06.ticket_number and
rec97.coupon=rec06.coupon) rec061
FULL OUTER JOIN ml_fnf_record98 rec98

/* here we are making alias for the first query and making again full outer join on this query*/

ON rec98.airline=rec061.airline and
rec98.ticket_number=rec061.ticket_number and
rec98.coupon=rec061.coupon
 
Last edited:
Back
Top