Trouble restricting results

Hi,

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.

bazz

yes

what happened when you tested it?

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

and then post-process it into the way I want it?

bazz

what you want is

ORDER
    BY level_1.id
     , level_2.id
     , level_3.id 

and the single level_1 will sort ahead of any level_1’s with the same value with level_2’s attached, since NULLs sort first

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

pondering over some sleeeeep.

bazz

I think I’m a numptie lol

The results being retrieved were correct. my script (well, me actually), was confusing the results at the output stage. doh!

thanks again for the assistance.

that’s pretty much what i was thinking in post #3

:slight_smile: