SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Code MySQL:
    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

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by IBazz View Post
    Is my query even close?
    yes

    what happened when you tested it?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    select id....
    from ...
    ORDER BY
    or

    Code:
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by IBazz View Post
    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.
    what you want is
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code MySQL:
    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

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by IBazz View Post
    The results being retrieved were correct.
    that's pretty much what i was thinking in post #3

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •