MySQL: Fetching non-null values using multiple joins

Question

I am running a few queries which I want to convert to a single query using joins My first query is

1)  SELECT * FROM ACT_TABLE1 where  node='5bbcdded' order by Instance_ID desc;

The output of the above query is as below

ID          Instance_ID NODE
2326600581  23266005612 5bbcdded1
2326524592  23265245712 5bbcdded2
2326523503  23265234213 5bbcdded3
2326523004  23265229614 5bbcdded4

2) Now, I grab topmost Instance_ID and run another select query as follows

SELECT * FROM ACT_TABLE2 where TOP_INST_ID = '23266005612';

Here, there might be a situation where select query returns a null value from above query. In that case, I grab second topmost Instance_ID and run same select query as follows

SELECT * FROM ACT_TABLE2 where TOP_INST_ID = '23265245712';

The output of the above query returns only single row as below

ID          NEXT_ID     TOP_INSTANCE_ID
232660056   232660056   232652457

3) Now, I grab topmost NEXT_ID and run another select query as follows

   SELECT * FROM ACT_TABLE3 where NEXT_ID = '232660056';

The output of the above query returns only single row as below

ID          EXEP_ID     NEXT_ID
232660072   232660139   232660056

4) Now, I grab topmost EXEP_ID and run another select query as follows

   SELECT field2 FROM ACT_TABLE4 where ID = '232660139';

The output of the above query returns field2 which is my final result

In other words, I want to pass node='5bbcdded' in my first table so that i can fetch value of field2 from my fourth table

Answer 1

• You can do Inner Join between all the tables, using their relationships.
• Then, employ multiple level Order By clauses starting from the first table (all in Descending order, since you want topmost from all the tables). We use LIMIT 1 to get the first row after sorting, which will be topmost.
• Inner Join will ensure that any non-matching rows (null in the next table) will be ignored.

Try:

SELECT t4.field2 
FROM ACT_TABLE1 AS t1 
INNER JOIN ACT_TABLE2 AS t2 ON t2.TOP_INST_ID = t1.Instance_ID
INNER JOIN ACT_TABLE3 AS t3 ON t3.NEXT_ID = t2.NEXT_ID 
INNER JOIN ACT_TABLE4 AS t4 ON t4.ID = t3.EXEP_ID 
where t1.node = '5bbcdded'
ORDER BY t1.Instance_ID DESC, t2.NEXT_ID DESC, t3.EXEP_ID DESC 
LIMIT 1