Query left outer join

J

Jared

I have the following query that I wish to return ALL vehicles and then show
all trips applicable to those vehicles for the current date (pulled from a
form). The problem I'm having is that the query returns only the vehicles
that have trips for the day, not ALL vehicles.

SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to,
trips.pickup_date, trips.drop_date, trips.closed, trips.timeField,
vehicle.SubContractor
FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField])
AND ((vehicle.SubContractor)=False) AND ((vehicle.sold)=False) AND
((vehicle.trailer)=False)) OR
(((trips.drop_date)=[Forms]![frmAllocation]![AllocationDateField]))
ORDER BY vehicle.vehicle_label, trips.timeField;


Thankyou in advance.
 
T

Tokyo Alex

Hi Jared,

Your WHERE clause is limiting by pickup_date or drop_date, so if there is no
date in these fields (there's no trip for that day) it will not return a
record.

I think you need something like (untested):
SELECT v.vehicle_label, t.trip_from, t.trip_to, t.pickup_date, t.drop_date,
t.closed, t.timeField, v.SubContractor
FROM vehicle AS v LEFT JOIN (
SELECT trip_from, trip_to, pickup_date, drop_date, closed, timeField,
vehicle_fkey
FROM trips
WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField] OR
trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]) AS t
ON v.vehicle_label = t.vehicle_fkey
WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
ORDER BY v.vehicle_label, t.timeField;

This uses a subquery to return all the trips with either a pickup_date or a
drop_date equal to the form parameter, and then left joins that result to the
vehicle table. It *should* work, but as I said, it's untested.

Cheers and hope this helps,
Alex.
 
J

Jared

Thankyou Alex, perfect.

Tokyo Alex said:
Hi Jared,

Your WHERE clause is limiting by pickup_date or drop_date, so if there is no
date in these fields (there's no trip for that day) it will not return a
record.

I think you need something like (untested):
SELECT v.vehicle_label, t.trip_from, t.trip_to, t.pickup_date, t.drop_date,
t.closed, t.timeField, v.SubContractor
FROM vehicle AS v LEFT JOIN (
SELECT trip_from, trip_to, pickup_date, drop_date, closed, timeField,
vehicle_fkey
FROM trips
WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField] OR
trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]) AS t
ON v.vehicle_label = t.vehicle_fkey
WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
ORDER BY v.vehicle_label, t.timeField;

This uses a subquery to return all the trips with either a pickup_date or a
drop_date equal to the form parameter, and then left joins that result to the
vehicle table. It *should* work, but as I said, it's untested.

Cheers and hope this helps,
Alex.


Jared said:
I have the following query that I wish to return ALL vehicles and then show
all trips applicable to those vehicles for the current date (pulled from a
form). The problem I'm having is that the query returns only the vehicles
that have trips for the day, not ALL vehicles.

SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to,
trips.pickup_date, trips.drop_date, trips.closed, trips.timeField,
vehicle.SubContractor
FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField])
AND ((vehicle.SubContractor)=False) AND ((vehicle.sold)=False) AND
((vehicle.trailer)=False)) OR
(((trips.drop_date)=[Forms]![frmAllocation]![AllocationDateField]))
ORDER BY vehicle.vehicle_label, trips.timeField;


Thankyou in advance.
Click to expand...
Click to expand...
 
J

Jared

Jared said:
Thankyou Alex, perfect.

Tokyo Alex said:
Hi Jared,

Your WHERE clause is limiting by pickup_date or drop_date, so if there is no
date in these fields (there's no trip for that day) it will not return a
record.

I think you need something like (untested):
SELECT v.vehicle_label, t.trip_from, t.trip_to, t.pickup_date, t.drop_date,
t.closed, t.timeField, v.SubContractor
FROM vehicle AS v LEFT JOIN (
SELECT trip_from, trip_to, pickup_date, drop_date, closed, timeField,
vehicle_fkey
FROM trips
WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField] OR
trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]) AS t
ON v.vehicle_label = t.vehicle_fkey
WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
ORDER BY v.vehicle_label, t.timeField;

This uses a subquery to return all the trips with either a pickup_date or a
drop_date equal to the form parameter, and then left joins that result to the
vehicle table. It *should* work, but as I said, it's untested.

Cheers and hope this helps,
Alex.


Jared said:
I have the following query that I wish to return ALL vehicles and then show
all trips applicable to those vehicles for the current date (pulled from a
form). The problem I'm having is that the query returns only the vehicles
that have trips for the day, not ALL vehicles.

SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to,
trips.pickup_date, trips.drop_date, trips.closed, trips.timeField,
vehicle.SubContractor
FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField])
AND ((vehicle.SubContractor)=False) AND ((vehicle.sold)=False) AND
((vehicle.trailer)=False)) OR
(((trips.drop_date)=[Forms]![frmAllocation]![AllocationDateField]))
ORDER BY vehicle.vehicle_label, trips.timeField;


Thankyou in advance.
Click to expand...
Click to expand...
Click to expand...

Hi Alex,

I need to add another table to the query called "custom_trips" and can't
seem to get the code right for the second left join

basically i need add:

LEFT JOIN SELECT *
FROM custom_trips
WHERE
(custom_trips.custom_date)=[Forms]![frmAllocation]!AllocationDateField])
as c ON v.vehicle_label = c.custom_vehicle_fkey

I can't seem to find any post where a second left join is included as a
subquery per your previous help.

Thanks

Jared
 
J

John Spencer

The SQL should look something like the following.

SELECT v.vehicle_label, t.trip_from, t.trip_to
, t.pickup_date, t.drop_date
, t.closed, t.timeField, v.SubContractor
FROM (vehicle AS v LEFT JOIN
(
SELECT trip_from, trip_to, pickup_date
, drop_date, closed, timeField, vehicle_fkey
FROM trips
WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField]
OR trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]
) AS t
ON v.vehicle_label = t.vehicle_fkey)
LEFT JOIN
(
SELECT *
FROM custom_trips
WHERE custom_trips.custom_date=
[Forms]![frmAllocation]!AllocationDateField]
) as c
ON v.vehicle_label = c.custom_vehicle_fkey

WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
ORDER BY v.vehicle_label, t.timeField;




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Jared said:
Thankyou Alex, perfect.

Tokyo Alex said:
Hi Jared,

Your WHERE clause is limiting by pickup_date or drop_date, so if there is no
date in these fields (there's no trip for that day) it will not return a
record.

I think you need something like (untested):
SELECT v.vehicle_label, t.trip_from, t.trip_to, t.pickup_date, t.drop_date,
t.closed, t.timeField, v.SubContractor
FROM vehicle AS v LEFT JOIN (
SELECT trip_from, trip_to, pickup_date, drop_date, closed, timeField,
vehicle_fkey
FROM trips
WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField] OR
trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]) AS t
ON v.vehicle_label = t.vehicle_fkey
WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
ORDER BY v.vehicle_label, t.timeField;

This uses a subquery to return all the trips with either a pickup_date or a
drop_date equal to the form parameter, and then left joins that result to the
vehicle table. It *should* work, but as I said, it's untested.

Cheers and hope this helps,
Alex.


:

I have the following query that I wish to return ALL vehicles and then show
all trips applicable to those vehicles for the current date (pulled from a
form). The problem I'm having is that the query returns only the vehicles
that have trips for the day, not ALL vehicles.

SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to,
trips.pickup_date, trips.drop_date, trips.closed, trips.timeField,
vehicle.SubContractor
FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField])
AND ((vehicle.SubContractor)=False) AND ((vehicle.sold)=False) AND
((vehicle.trailer)=False)) OR
(((trips.drop_date)=[Forms]![frmAllocation]![AllocationDateField]))
ORDER BY vehicle.vehicle_label, trips.timeField;


Thankyou in advance.
Click to expand...
Click to expand...

Hi Alex,

I need to add another table to the query called "custom_trips" and can't
seem to get the code right for the second left join

basically i need add:

LEFT JOIN SELECT *
FROM custom_trips
WHERE
(custom_trips.custom_date)=[Forms]![frmAllocation]!AllocationDateField])
as c ON v.vehicle_label = c.custom_vehicle_fkey

I can't seem to find any post where a second left join is included as a
subquery per your previous help.

Thanks

Jared
Click to expand...
 
J

Jared

Thankyou John. Works great. just had to add a [ in the where clause.

Thanks for the quick response too. This forum is an extremely valuable
resource.



John Spencer said:
The SQL should look something like the following.

SELECT v.vehicle_label, t.trip_from, t.trip_to
, t.pickup_date, t.drop_date
, t.closed, t.timeField, v.SubContractor
FROM (vehicle AS v LEFT JOIN
(
SELECT trip_from, trip_to, pickup_date
, drop_date, closed, timeField, vehicle_fkey
FROM trips
WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField]
OR trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]
) AS t
ON v.vehicle_label = t.vehicle_fkey)
LEFT JOIN
(
SELECT *
FROM custom_trips
WHERE custom_trips.custom_date=
[Forms]![frmAllocation]!AllocationDateField]
) as c
ON v.vehicle_label = c.custom_vehicle_fkey

WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
ORDER BY v.vehicle_label, t.timeField;




John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Jared said:
Thankyou Alex, perfect.

:

Hi Jared,

Your WHERE clause is limiting by pickup_date or drop_date, so if there is no
date in these fields (there's no trip for that day) it will not return a
record.

I think you need something like (untested):
SELECT v.vehicle_label, t.trip_from, t.trip_to, t.pickup_date, t.drop_date,
t.closed, t.timeField, v.SubContractor
FROM vehicle AS v LEFT JOIN (
SELECT trip_from, trip_to, pickup_date, drop_date, closed, timeField,
vehicle_fkey
FROM trips
WHERE trips.pickup_date=[Forms]![frmAllocation]![AllocationDateField] OR
trips.drop_date=[Forms]![frmAllocation]![AllocationDateField]) AS t
ON v.vehicle_label = t.vehicle_fkey
WHERE v.SubContractor=False AND v.sold=False AND v.trailer=False
ORDER BY v.vehicle_label, t.timeField;

This uses a subquery to return all the trips with either a pickup_date or a
drop_date equal to the form parameter, and then left joins that result to the
vehicle table. It *should* work, but as I said, it's untested.

Cheers and hope this helps,
Alex.


:

I have the following query that I wish to return ALL vehicles and then show
all trips applicable to those vehicles for the current date (pulled from a
form). The problem I'm having is that the query returns only the vehicles
that have trips for the day, not ALL vehicles.

SELECT vehicle.vehicle_label, trips.trip_from, trips.trip_to,
trips.pickup_date, trips.drop_date, trips.closed, trips.timeField,
vehicle.SubContractor
FROM vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
WHERE (((trips.pickup_date)=[Forms]![frmAllocation]![AllocationDateField])
AND ((vehicle.SubContractor)=False) AND ((vehicle.sold)=False) AND
((vehicle.trailer)=False)) OR
(((trips.drop_date)=[Forms]![frmAllocation]![AllocationDateField]))
ORDER BY vehicle.vehicle_label, trips.timeField;


Thankyou in advance.
Click to expand...

Hi Alex,

I need to add another table to the query called "custom_trips" and can't
seem to get the code right for the second left join

basically i need add:

LEFT JOIN SELECT *
FROM custom_trips
WHERE
(custom_trips.custom_date)=[Forms]![frmAllocation]!AllocationDateField])
as c ON v.vehicle_label = c.custom_vehicle_fkey

I can't seem to find any post where a second left join is included as a
subquery per your previous help.

Thanks

Jared
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Relationship\Multiple Join 5
Query with left outer join all of a sudden won't work? 16
Insert query with two left outer joins gives "Record is Deleted" m 0
Left Join query + form--updateable? 1
left join problem in XP 4

Top