I have a grandparent, parent and child relationship. Each is a business_id. each business may (or may not), have a stylesheet record in the second table.
I will be entering an id to the WHERE clause. I need to bring back those ids on senior levels, which do have a record in table_2.
so, if a child_id is say 32 and its parent records each has a record in table_2 (AND the live_or_dev col is ‘live’), I need to get those ids, in order from grandparent->parent->child - or even grandparent->child OR grandparent->parent. Just whatever ids have a stylesheet
I can’t get my head around it but it seems to be join issue but I am not sure.
SELECT
level_3.id as id_3
, level_2.id as id_2
, level_1.id as id_1
from businesses AS level_1
left outer
JOIN businesses as level_2
on level_2.id = level_1.parent_id
left outer
join stylesheets as L2_s
on L2_s.business_id = level_2.id
and L2_s.live_or_dev = 'live'
left outer
JOIN businesses as level_3
on level_3.id = level_2.parent_id
left outer
join stylesheets as L3_s
on L3_s.business_id = level_3.id
and L3_s.live_or_dev = 'live'
left outer
join stylesheets as L1_s
on L1_s.business_id = level_1.id
and L1_s.live_or_dev = 'live'
WHERE level_1.id = 7
to clarify, I want this child record and any parents and grandparents. But I don;t want any siblings or children of the id entered in the where clause.
Is my query even close? maybe I need think about sub selects? I moved away from the union idea that I had, because the third part of the union would be pretty much a duplicate of the first and second levels.
well, it seems to get me the data I want. However, if there is a trio of results (Gparent, parent and child), I need it to order in that sequence. But when there is just one value it’ll be level_1 and I need it to output first.
Maybe I’ll have to post process it OR perhaps, there is a way to select one column (which contains all the values anyway) and order them with an ORDER BY clause.
That’s the bit that has me cafuddled.
should I aim for
select id....
from ...
ORDER BY
or
select
level_1.id as L1_id
level_2.id as L2_id
level_3.id as L3_id
Thanks again.
OK, for the purposes of ym test environment, the figures are:
grandparent 56
parent 170
child 385 and 473
Now; if there is just one level to show because I use 56 in the where clause, the query shows it as level_1, which is fine.
However, if there is more than 1 level, say when I enter 170 or 385 in the where clause, each derivative of resultset is like this:
56
170,56
385,170,56
I need it to be
56, 170, 385
But, if I simply swap around the first three lines of the query, to get that sort of order, it puts a single entity result (56) as a third value with the two before it, being null
SELECT
level_1.id as id_1
, level_2.id as id_2
, level_3.id as id_3
FROM businesses AS level_1
LEFT OUTER
JOIN businesses as level_2
on level_2.id = level_1.parent_id
LEFT OUTER
JOIN stylesheets as L2_s
on L2_s.business_id = level_2.id
AND L2_s.live_or_dev = 'live'
LEFT OUTER
JOIN businesses as level_3
on level_3.id = level_2.parent_id
LEFT OUTER
JOIN stylesheets as L3_s
on L3_s.business_id = level_3.id
AND L3_s.live_or_dev = 'live'
LEFT OUTER
JOIN stylesheets as L1_s
on L1_s.business_id = level_1.id
AND L1_s.live_or_dev = 'live'
WHERE level_1.id = 385