help with 3 table relationship join

[TruthInSatire]

Access produced the following query. It works but does not return what I need.

it returns zero records because the thrid table is empty (coiValues). I need it to display all the records from the first and second tables (coi, coiFields) reguardless.

here is the current query:

SELECT coi.coiName, coi.coiID, coiFields.fieldID, coiFields.fieldName, coiFields.priority, coiValues.valueID, coiValues.fieldValue, coiValues.coiNumber
FROM (coi INNER JOIN coiFields ON coi.coiID = coiFields.coiID) INNER JOIN coiValues ON (coi.coiID = coiValues.coiID) AND (coiFields.fieldID = coiValues.fieldID)

thanks in advance

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[willir]

bombboy

Sad thing about Douglas Adams passing, way before his time.

Your design looks interesting, and may be why you are not getting any returned records.


[tt]
coi coiValues
coi.coiID ------------+ ValueID
coi.coName ¦ +--------- coiID
¦ +----- fieldID
¦ ¦ fieldValue
¦ coiFields ¦ coiNumber
¦ fieldID ---+
+- coiID
fieldName
Priority

[/tt]

You are joining the coiValue from the coi table directly, and indirectly via the coiFields table.

Try simplifying the
...INNER JOIN coiValues ON (coi.coiID = coiValues.coiID) AND (coiFields.fieldID = coiValues.fieldID)

to...
INNER JOIN coiValues ON (coiFields.fieldID = coiValues.fieldID)

I took your design, and retreived 4 records with the first query, dropped the coi - coiValue reference and retrieved all 21 values in the coiValue table.

Sometimes the direct reference to table that is otherwise accesible indirectly to effeciency, but you need to be aware that it kind of breaks normalization.

Richard