I have two query 1) tree and 2) units
first query
SELECT * FROM tree WHERE entity_id = 8656
return below data:
entity id | parent_id | length
-------------------------------
8656 | 8656 | 0
8656 | 8655 | 1
8656 | 8654 | 2
8656 | 8653 | 3
8656 | 4331 | 4
8656 | 2 | 5
8656 | 1 | 6
second query
SELECT * FROM units WHERE activity_id =10066
returns
id | activity_id | activity_name | region_id | region_name
-----------------------------------------------------------
136 | 10066 | Cricket | 4331 | Yote
137 | 10066 | Cricket | 8653 | Handbreath
140 | 10066 | Cricket | 8656 | Kevb
result expected
entity id | parent_id | length | region_name
--------------------------------------------
8656 | 8656 | 0 | Kevb
8656 | 8655 | 1 | null
8656 | 8654 | 2 | null
8656 | 8653 | 3 | Handbreath
8656 | 4331 | 4 | Yote
8656 | 2 | 5 | null
8656 | 1 | 6 | null
I have tried this query
SELECT tree . * , units.region_name
FROM tree
LEFT JOIN units ON tree.parent_id = units.region_id
WHERE tree.entity_id = 8656
AND units.activity_id = 10066
By moving the
u.activity_id =10066
to the LEFT JOIN
and not the WHERE
clause, I am able to produce the result:select t.entity_id,
t.parent_id,
t.length,
u.region_name
from tree t
left join units u
on t.parent_id = u.region_id
and u.activity_id =10066
WHERE t.entity_id = 8656
0 comments:
Post a Comment