Access Query Left join with Inner join and 'And' Clause-CodePudding

I have 3 tables I'm trying to join, and the final table may be missing a record. I'm trying to determine if it's there or not. Notice how table 3 doesn't have a SignalType of 10 and a ConversionParameter of 38. So my result set should have 4 records, one of which should have nulls for table 3.

Table 1:

id	Algorithm
37	40
38	40

Table 2:

id	ConversionAlgorithm
10	40
11	40

Table 3:

id	SignalType	ConversionParameter	Value
1	10	37	0
2	11	37	0
3	11	38	1

I started with a query as follows, which gives me 4 records as expected.

select * from (table1 t1
inner join table2 t2 on t2.conversionalgorithm = t1.algorithm)

I then try to left join this with table 3:

select * from (table1 t1
inner join table2 t2 on t2.conversionalgorithm = t1.algorithm)
left join table3 t3 on (t1.id = t3.conversionparameter) and (t2.id = t3.SignalType)

but this gives me an error that JOIN expression is not supported.

However, if I change my left join to an inner join, it does return a record set, but it only returns 3 records. I'm confused when an inner join works but left join fails.

select * from (table1 t1
inner join table2 t2 on t2.conversionalgorithm = t1.algorithm)
**inner** join table3 t3 on (t1.id = t3.conversionparameter) and (t2.id = t3.SignalType)

I want a final recordset that looks like the following, where a record with t3.signaltype 10 and t3.conversionparameter 38 is missing

t1.id	t2.id	t1.algorithm	t2.algorithm	t3.id	SignalType	ConversionParameter	Value
37	10	40	40	1	10	37	0
38	11	40	40	2	11	37	0
37	11	40	40	3	11	38	1
38	10	40	40	null	null	null	null

EDIT:

Exporting the data to mysql, the following query works as expected, which pains me since it's probably an Access formatting issue and I'm tied to MS Access.

select * from table1 t1
inner join table2 t2 on t2.conversionalgorithm = t1.algorithm
left join table3 t3 on t3.ConversionParameter = t1.id and t3.logicalsignaltype = t2.id;

CodePudding user response：

Consider RIGHT JOIN with nested query:

SELECT *
FROM Table3 
RIGHT JOIN (
           SELECT *
           FROM Table1 INNER JOIN Table2 
           ON Table1.algorithm = Table2.ConversionAlgorithm) AS Query1 
ON (Table3.ConversionParameter = Query1.Table1.id) AND (Table3.SignalType = Query1.Table2.id);

The algorithm fields do not have unique values so joining causes a Cartesian association of records - all records of each table for each algorithm value are associated with all records of same value in other table. This provides every possible combination of Table1.id and Table2.id and algorithm data. Compound RIGHT JOIN of that query with Table3 then shows which SignalType and ConversionParameter pairs are missing in Table3.

CodePudding user response：

Credit to @June7 for sending me down the right path. Here is the solution I was able to come up with using his suggestion of subselects:

    select * from (
       select * from table1 t1
       inner join table2 t2 on t2.conversionalgorithm = t1.algorithm) as q
       left join table3 t3 on (t3.conversionparameter = q.t1.id) and (q.t2.id = t3.logicalsignaltype)