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 tblPartNumberVe ndor (since more than one vendor can
make the part), then left joining to tblPartNumberSu pplier (since more
than one supplier can distribute the vendor's part), then left joining
to tblPartNumberCo st (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.P artNumberID, tblPartNumber.f ldPartNumber,
tblPartNumber.f ldDescription, tblPartNumberVe ndor.VendorID,
tblPartNumberVe ndor.fldPartNum berVendor,
tblPartNumberSu pplier.Supplier ID,
tblPartNumberSu pplier.fldPartN umberSupplier,
tblPartNumberCo st.fldUnits, tblPartNumberCo st.fldCostCDN,
tblPartNumberCo st.fldCostUS
FROM (tblPartNumber LEFT JOIN tblPartNumberVe ndor ON
tblPartNumber.P artNumberID = tblPartNumberVe ndor.PartNumber ID) LEFT
JOIN (tblPartNumberS upplier LEFT JOIN tblPartNumberCo st ON
tblPartNumberSu pplier.PartNumb erSupplierID =
tblPartNumberCo st.PartNumberSu pplierID) ON
tblPartNumberVe ndor.PartNumber VendorID =
tblPartNumberSu pplier.PartNumb erVendorID;

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

SELECT tblPartNumber.P artNumberID, tblPartNumber.f ldPartNumber,
tblPartNumber.f ldDescription, tblPartNumberVe ndor.VendorID,
tblPartNumberVe ndor.fldPartNum berVendor,
tblPartNumberSu pplier.Supplier ID,
tblPartNumberSu pplier.fldPartN umberSupplier,
tblPartNumberCo st.fldUnits, tblPartNumberCo st.fldCostCDN,
tblPartNumberCo st.fldCostUS
FROM (tblPartNumber LEFT JOIN tblPartNumberVe ndor ON
(tblPartNumber. PartNumberID = tblPartNumberVe ndor.PartNumber ID AND
tblPartNumberVe ndor.fldPrimary Vendor = yes)) LEFT JOIN
(tblPartNumberS upplier LEFT JOIN tblPartNumberCo st ON
(tblPartNumberS upplier.PartNum berSupplierID =
tblPartNumberCo st.PartNumberSu pplierID AND
tblPartNumberCo st.fldPrimaryCo st = yes)) ON
(tblPartNumberV endor.PartNumbe rVendorID =
tblPartNumberSu pplier.PartNumb erVendorID AND
tblPartNumberSu pplier.fldPrima rySupplier = 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.c om wrote in
news:11******** **************@ c74g2000cwc.goo glegroups.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 tblPartNumberVe ndor (since
more than one vendor can make the part), then left joining to
tblPartNumberSu pplier (since more than one supplier can
distribute the vendor's part), then left joining to
tblPartNumberCo st (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.P artNumberID,
tblPartNumber.f ldPartNumber, tblPartNumber.f ldDescription,
tblPartNumberVe ndor.VendorID,
tblPartNumberVe ndor.fldPartNum berVendor,
tblPartNumberSu pplier.Supplier ID,
tblPartNumberSu pplier.fldPartN umberSupplier,
tblPartNumberCo st.fldUnits, tblPartNumberCo st.fldCostCDN,
tblPartNumberCo st.fldCostUS FROM (tblPartNumber LEFT JOIN
tblPartNumberVe ndor ON tblPartNumber.P artNumberID =
tblPartNumberVe ndor.PartNumber ID) LEFT JOIN
(tblPartNumberS upplier LEFT JOIN tblPartNumberCo st ON
tblPartNumberSu pplier.PartNumb erSupplierID =
tblPartNumberCo st.PartNumberSu pplierID) ON
tblPartNumberVe ndor.PartNumber VendorID =
tblPartNumberSu pplier.PartNumb erVendorID;

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

SELECT tblPartNumber.P artNumberID,
tblPartNumber.f ldPartNumber, tblPartNumber.f ldDescription,
tblPartNumberVe ndor.VendorID,
tblPartNumberVe ndor.fldPartNum berVendor,
tblPartNumberSu pplier.Supplier ID,
tblPartNumberSu pplier.fldPartN umberSupplier,
tblPartNumberCo st.fldUnits, tblPartNumberCo st.fldCostCDN,
tblPartNumberCo st.fldCostUS FROM (tblPartNumber LEFT JOIN
tblPartNumberVe ndor ON (tblPartNumber. PartNumberID =
tblPartNumberVe ndor.PartNumber ID AND
tblPartNumberVe ndor.fldPrimary Vendor = yes)) LEFT JOIN
(tblPartNumberS upplier LEFT JOIN tblPartNumberCo st ON
(tblPartNumberS upplier.PartNum berSupplierID =
tblPartNumberCo st.PartNumberSu pplierID AND
tblPartNumberCo st.fldPrimaryCo st = yes)) ON
(tblPartNumberV endor.PartNumbe rVendorID =
tblPartNumberSu pplier.PartNumb erVendorID AND
tblPartNumberSu pplier.fldPrima rySupplier = 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
(
tblPartNumberVe ndor.fldPrimary is TRUE
OR
tblPartNumberVe ndor.fldPrimary is NULL
)
AND
(
tblPartNumberSu pplier.fld primary is TRUE
OR
tblPartNumberSu pplier.fld primary is NULL
)
AND
(
tblPartNumberCo st.fld primary is NULL
OR
tblPartNumberCo st.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.c om wrote in
news:11******** **************@ c74g2000cwc.goo glegroups.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 tblPartNumberVe ndor (since
more than one vendor can make the part), then left joining to
tblPartNumberSu pplier (since more than one supplier can
distribute the vendor's part), then left joining to
tblPartNumberCo st (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.P artNumberID,
tblPartNumber.f ldPartNumber, tblPartNumber.f ldDescription,
tblPartNumberVe ndor.VendorID,
tblPartNumberVe ndor.fldPartNum berVendor,
tblPartNumberSu pplier.Supplier ID,
tblPartNumberSu pplier.fldPartN umberSupplier,
tblPartNumberCo st.fldUnits, tblPartNumberCo st.fldCostCDN,
tblPartNumberCo st.fldCostUS FROM (tblPartNumber LEFT JOIN
tblPartNumberVe ndor ON tblPartNumber.P artNumberID =
tblPartNumberVe ndor.PartNumber ID) LEFT JOIN
(tblPartNumberS upplier LEFT JOIN tblPartNumberCo st ON
tblPartNumberSu pplier.PartNumb erSupplierID =
tblPartNumberCo st.PartNumberSu pplierID) ON
tblPartNumberVe ndor.PartNumber VendorID =
tblPartNumberSu pplier.PartNumb erVendorID;

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

SELECT tblPartNumber.P artNumberID,
tblPartNumber.f ldPartNumber, tblPartNumber.f ldDescription,
tblPartNumberVe ndor.VendorID,
tblPartNumberVe ndor.fldPartNum berVendor,
tblPartNumberSu pplier.Supplier ID,
tblPartNumberSu pplier.fldPartN umberSupplier,
tblPartNumberCo st.fldUnits, tblPartNumberCo st.fldCostCDN,
tblPartNumberCo st.fldCostUS FROM (tblPartNumber LEFT JOIN
tblPartNumberVe ndor ON (tblPartNumber. PartNumberID =
tblPartNumberVe ndor.PartNumber ID AND
tblPartNumberVe ndor.fldPrimary Vendor = yes)) LEFT JOIN
(tblPartNumberS upplier LEFT JOIN tblPartNumberCo st ON
(tblPartNumberS upplier.PartNum berSupplierID =
tblPartNumberCo st.PartNumberSu pplierID AND
tblPartNumberCo st.fldPrimaryCo st = yes)) ON
(tblPartNumberV endor.PartNumbe rVendorID =
tblPartNumberSu pplier.PartNumb erVendorID AND
tblPartNumberSu pplier.fldPrima rySupplier = 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
(
tblPartNumberVe ndor.fldPrimary is TRUE
OR
tblPartNumberVe ndor.fldPrimary is NULL
)
AND
(
tblPartNumberSu pplier.fld primary is TRUE
OR
tblPartNumberSu pplier.fld primary is NULL
)
AND
(
tblPartNumberCo st.fld primary is NULL
OR
tblPartNumberCo st.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