Left Join with criteria (long msg)

[tricard]

Good day all,

I have a large outer joined query that I want to have some criteria.
The select query is gathering all part numbers from tblPartNumbers,
left joining to tblPartNumberVendor (since more than one vendor can
make the part), then left joining to tblPartNumberSupplier (since more
than one supplier can distribute the vendor's part), then left joining
to tblPartNumberCost (since more than one cost can be associated with a
single supplier if there are pricebreaks). Now, each of these have a
field named fldPrimary. I want to limit this join to only show the
primary vendor, primary supplier for the primary vendor and the primary
cost for the primary supplier, but if there is no entry for the part
number to still show the part number. Here is my current working SQL
statement:

SELECT tblPartNumber.PartNumberID, tblPartNumber.fldPartNumber,
tblPartNumber.fldDescription, tblPartNumberVendor.VendorID,
tblPartNumberVendor.fldPartNumberVendor,
tblPartNumberSupplier.SupplierID,
tblPartNumberSupplier.fldPartNumberSupplier,
tblPartNumberCost.fldUnits, tblPartNumberCost.fldCostCDN,
tblPartNumberCost.fldCostUS
FROM (tblPartNumber LEFT JOIN tblPartNumberVendor ON
tblPartNumber.PartNumberID = tblPartNumberVendor.PartNumberID) LEFT
JOIN (tblPartNumberSupplier LEFT JOIN tblPartNumberCost ON
tblPartNumberSupplier.PartNumberSupplierID =
tblPartNumberCost.PartNumberSupplierID) ON
tblPartNumberVendor.PartNumberVendorID =
tblPartNumberSupplier.PartNumberVendorID;

I tried adding AND criteria within each left join like this:

SELECT tblPartNumber.PartNumberID, tblPartNumber.fldPartNumber,
tblPartNumber.fldDescription, tblPartNumberVendor.VendorID,
tblPartNumberVendor.fldPartNumberVendor,
tblPartNumberSupplier.SupplierID,
tblPartNumberSupplier.fldPartNumberSupplier,
tblPartNumberCost.fldUnits, tblPartNumberCost.fldCostCDN,
tblPartNumberCost.fldCostUS
FROM (tblPartNumber LEFT JOIN tblPartNumberVendor ON
(tblPartNumber.PartNumberID = tblPartNumberVendor.PartNumberID AND
tblPartNumberVendor.fldPrimaryVendor = yes)) LEFT JOIN
(tblPartNumberSupplier LEFT JOIN tblPartNumberCost ON
(tblPartNumberSupplier.PartNumberSupplierID =
tblPartNumberCost.PartNumberSupplierID AND
tblPartNumberCost.fldPrimaryCost = yes)) ON
(tblPartNumberVendor.PartNumberVendorID =
tblPartNumberSupplier.PartNumberVendorID AND
tblPartNumberSupplier.fldPrimarySupplier = yes);

But this still only returned a full outer join on each. I also tried
adding the criteria to the WHERE clause, but this demoted the outer
joins to inner joins. is there any way I can do this outer join in
access?

Thanks for the help and the time

Tim

[Bob Quintal]

tr*****@gmail.com wrote in
news:11**********************@c74g2000cwc.googlegr oups.com:

Good day all,

I have a large outer joined query that I want to have some
criteria. The select query is gathering all part numbers from
tblPartNumbers, left joining to tblPartNumberVendor (since
more than one vendor can make the part), then left joining to
tblPartNumberSupplier (since more than one supplier can
distribute the vendor's part), then left joining to
tblPartNumberCost (since more than one cost can be associated
with a single supplier if there are pricebreaks). Now, each of
these have a field named fldPrimary. I want to limit this join
to only show the primary vendor, primary supplier for the
primary vendor and the primary cost for the primary supplier,
but if there is no entry for the part number to still show the
part number. Here is my current working SQL statement:

SELECT tblPartNumber.PartNumberID,
tblPartNumber.fldPartNumber, tblPartNumber.fldDescription,
tblPartNumberVendor.VendorID,
tblPartNumberVendor.fldPartNumberVendor,
tblPartNumberSupplier.SupplierID,
tblPartNumberSupplier.fldPartNumberSupplier,
tblPartNumberCost.fldUnits, tblPartNumberCost.fldCostCDN,
tblPartNumberCost.fldCostUS FROM (tblPartNumber LEFT JOIN
tblPartNumberVendor ON tblPartNumber.PartNumberID =
tblPartNumberVendor.PartNumberID) LEFT JOIN
(tblPartNumberSupplier LEFT JOIN tblPartNumberCost ON
tblPartNumberSupplier.PartNumberSupplierID =
tblPartNumberCost.PartNumberSupplierID) ON
tblPartNumberVendor.PartNumberVendorID =
tblPartNumberSupplier.PartNumberVendorID;

I tried adding AND criteria within each left join like this:

SELECT tblPartNumber.PartNumberID,
tblPartNumber.fldPartNumber, tblPartNumber.fldDescription,
tblPartNumberVendor.VendorID,
tblPartNumberVendor.fldPartNumberVendor,
tblPartNumberSupplier.SupplierID,
tblPartNumberSupplier.fldPartNumberSupplier,
tblPartNumberCost.fldUnits, tblPartNumberCost.fldCostCDN,
tblPartNumberCost.fldCostUS FROM (tblPartNumber LEFT JOIN
tblPartNumberVendor ON (tblPartNumber.PartNumberID =
tblPartNumberVendor.PartNumberID AND
tblPartNumberVendor.fldPrimaryVendor = yes)) LEFT JOIN
(tblPartNumberSupplier LEFT JOIN tblPartNumberCost ON
(tblPartNumberSupplier.PartNumberSupplierID =
tblPartNumberCost.PartNumberSupplierID AND
tblPartNumberCost.fldPrimaryCost = yes)) ON
(tblPartNumberVendor.PartNumberVendorID =
tblPartNumberSupplier.PartNumberVendorID AND
tblPartNumberSupplier.fldPrimarySupplier = yes);

But this still only returned a full outer join on each. I also
tried adding the criteria to the WHERE clause, but this
demoted the outer joins to inner joins. is there any way I can
do this outer join in access?

Thanks for the help and the time

Tim

Yes. You have to add the where clause to return where the field
is null. So starting from your first example

WHERE
(
tblPartNumberVendor.fldPrimary is TRUE
OR
tblPartNumberVendor.fldPrimary is NULL
)
AND
(
tblPartNumberSupplier.fld primary is TRUE
OR
tblPartNumberSupplier.fld primary is NULL
)
AND
(
tblPartNumberCost.fld primary is NULL
OR
tblPartNumberCost.fld primary is NULL
)
The nesting of OR inside AND is very important.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[tricard]

Right On!! That did the trick. Thank you very much for the help

Tim

Bob Quintal wrote:

tr*****@gmail.com wrote in
news:11**********************@c74g2000cwc.googlegr oups.com:

Good day all,

I have a large outer joined query that I want to have some
criteria. The select query is gathering all part numbers from
tblPartNumbers, left joining to tblPartNumberVendor (since
more than one vendor can make the part), then left joining to
tblPartNumberSupplier (since more than one supplier can
distribute the vendor's part), then left joining to
tblPartNumberCost (since more than one cost can be associated
with a single supplier if there are pricebreaks). Now, each of
these have a field named fldPrimary. I want to limit this join
to only show the primary vendor, primary supplier for the
primary vendor and the primary cost for the primary supplier,
but if there is no entry for the part number to still show the
part number. Here is my current working SQL statement:

SELECT tblPartNumber.PartNumberID,
tblPartNumber.fldPartNumber, tblPartNumber.fldDescription,
tblPartNumberVendor.VendorID,
tblPartNumberVendor.fldPartNumberVendor,
tblPartNumberSupplier.SupplierID,
tblPartNumberSupplier.fldPartNumberSupplier,
tblPartNumberCost.fldUnits, tblPartNumberCost.fldCostCDN,
tblPartNumberCost.fldCostUS FROM (tblPartNumber LEFT JOIN
tblPartNumberVendor ON tblPartNumber.PartNumberID =
tblPartNumberVendor.PartNumberID) LEFT JOIN
(tblPartNumberSupplier LEFT JOIN tblPartNumberCost ON
tblPartNumberSupplier.PartNumberSupplierID =
tblPartNumberCost.PartNumberSupplierID) ON
tblPartNumberVendor.PartNumberVendorID =
tblPartNumberSupplier.PartNumberVendorID;

I tried adding AND criteria within each left join like this:

SELECT tblPartNumber.PartNumberID,
tblPartNumber.fldPartNumber, tblPartNumber.fldDescription,
tblPartNumberVendor.VendorID,
tblPartNumberVendor.fldPartNumberVendor,
tblPartNumberSupplier.SupplierID,
tblPartNumberSupplier.fldPartNumberSupplier,
tblPartNumberCost.fldUnits, tblPartNumberCost.fldCostCDN,
tblPartNumberCost.fldCostUS FROM (tblPartNumber LEFT JOIN
tblPartNumberVendor ON (tblPartNumber.PartNumberID =
tblPartNumberVendor.PartNumberID AND
tblPartNumberVendor.fldPrimaryVendor = yes)) LEFT JOIN
(tblPartNumberSupplier LEFT JOIN tblPartNumberCost ON
(tblPartNumberSupplier.PartNumberSupplierID =
tblPartNumberCost.PartNumberSupplierID AND
tblPartNumberCost.fldPrimaryCost = yes)) ON
(tblPartNumberVendor.PartNumberVendorID =
tblPartNumberSupplier.PartNumberVendorID AND
tblPartNumberSupplier.fldPrimarySupplier = yes);

But this still only returned a full outer join on each. I also
tried adding the criteria to the WHERE clause, but this
demoted the outer joins to inner joins. is there any way I can
do this outer join in access?

Thanks for the help and the time

Tim

Yes. You have to add the where clause to return where the field
is null. So starting from your first example

WHERE
(
tblPartNumberVendor.fldPrimary is TRUE
OR
tblPartNumberVendor.fldPrimary is NULL
)
AND
(
tblPartNumberSupplier.fld primary is TRUE
OR
tblPartNumberSupplier.fld primary is NULL
)
AND
(
tblPartNumberCost.fld primary is NULL
OR
tblPartNumberCost.fld primary is NULL
)
The nesting of OR inside AND is very important.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com