Inner join on left join query 2

[dreampolice]

My Access 2000 query works great in the below with the left join on TableThree.
It fetches the data and also gets the status for TableTwo using the INNER JOIN:
INNER JOIN Status as myStatus on myTableTwo.status_id = myStatus.status_id

SELECT *
FROM ((TableTwo AS myTableTwo
INNER JOIN TableOne AS myTableOne 
ON myTableTwo.tableOne_id = myTableOne.tableOne_id)
INNER JOIN Status as myStatus on myTableTwo.status_id = myStatus.status_id)
LEFT JOIN TableThree as myTableThree on myTableTwo.tableTwo_id = myTableThree.tableTwo_id
WHERE myTableOne.tableOne_id = 3;

I now need to get status using the TableThree status_id if I have data in TableThree, and need to get it from the Status table.
My attempt is not working:

SELECT *
FROM ((TableTwo AS myTableTwo
INNER JOIN TableOne AS myTableOne 
ON myTableTwo.tableOne_id = myTableOne.tableOne_id)
INNER JOIN Status as myStatus on myTableTwo.status_id = myStatus.status_id)
LEFT JOIN TableThree as myTableThree on myTableTwo.tableTwo_id = myTableThree.tableTwo_id
[COLOR=green](INNER JOIN myTableThree.status_id = myStatus.status_id)[/color]
WHERE myTableOne.tableOne_id = 3;

Here are my 4 Tables:
TableOne
tableOne_id PK

TableTwo
tableTwo_id PK
tableOne_id FK
status_id

TableThree
tableThree_id PK
tableTwo_id FK
tableOne_id FK
status_id

Status
status_id PK
status

PK=Primary Key
FK=Foreign Key

Please advise.

 

[r937]

not sure i understand completely what you're asking...

SELECT *
  FROM (
       (
       TableOne AS myTableOne 
INNER 
  JOIN TableTwo AS myTableTwo
    ON myTableTwo.tableOne_id = myTableOne.tableOne_id
       )
LEFT 
  JOIN TableThree as myTableThree 
    on myTableThree.tableTwo_id = myTableTwo.tableTwo_id 
       )
INNER 
  JOIN Status as myStatus 
    on myStatus.status_id = 
         iif(isnull(myTableThree.tableTwo_id)
             , myTableTwo.status_id
             , myTableThree.status_id)
       )
 WHERE myTableOne.tableOne_id = 3;

r937.com | rudy.ca

 

[dreampolice]

Thanks!

The query gave me an error message back saying "Join expression not supported".


Example of what is in my 4 Tables:

[b]TableOne[/b]
tableOne_id   name
1             Jones
2             Smith
3             Carson
	
	
[b]TableTwo[/b]
tableTwo_id   tableOne_id  status_id   WorkingJob
1             1            5           Job Ad
2             1            4           Other Job



[b]TableThree[/b]
tableThree_id  tableTwo_id   status_id   MaintenanceInfo
1              1             3           extra info Job Ad

		
[b]Status[/b]
status_id   status
3           Finished
4           In Work
5           Starting

I can currently fetch TableTwo status info with TableOne and TableThree info such as:
tableTwo_id number 1 would have a status with Starting and the name of the
person from TableOne would be Jones and the TableThree info would also be available if
there was data in TableThree. But I can only get the TableThree status_id and not the
actual status info from the Status table. In this case the Status for TableThree would be
Finished which I cant fetch in my query.


Please advise because I have tried several other queries and cant seem to get it to work.

 

[PHV]

And what about this ?

SELECT *
FROM Status AS myStatus, (TableTwo AS myTableTwo
INNER JOIN TableOne AS myTableOne ON myTableTwo.tableOne_id = myTableOne.tableOne_id)
LEFT JOIN TableThree AS myTableThree ON myTableTwo.tableTwo_id = myTableThree.tableTwo_id
WHERE myStatus.status_id = Nz(myTableThree.status_id, myTableTwo.status_id)
AND myTableOne.tableOne_id = 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dreampolice]

Thanks PHV,

I tried exactly as suggested and it gave me this error:
Join Expression not supported

Is there anything I can change to make that query work?

 

[PHV]

Join Expression not supported
Which expression ?

Perhaps this ?
SELECT *
FROM (((TableTwo AS myTableTwo
INNER JOIN Status AS myStatus2 ON myTableTwo.status_id = myStatus2.status_id)
INNER JOIN TableOne AS myTableOne ON myTableTwo.tableOne_id = myTableOne.tableOne_id)
LEFT JOIN TableThree AS myTableThree ON myTableTwo.tableTwo_id = myTableThree.tableTwo_id)
LEFT JOIN Status AS myStatus3 ON myTableThree.status_id = myStatus3.status_id
WHERE myTableOne.tableOne_id = 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dreampolice]

It works.

Thanks PHV!!!

Thanks for all your time and the query suggestions in this post.