SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Thread: MySQL JOIN help

  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL JOIN help

    Hey guys, been messing with this for a while, getting close but still not getting the results I need. I have two tables, pages, and sections.

    Pages looks like this:
    id | parent_id | page_number

    Sections:
    id | parent_id | page_id | type | etc

    I need to select all sections where the parent_id = 1 and also include the page_number for all sections where sections.parent_id = pages.parent_id

    This is what I have:

    Code:
    SELECT *
    FROM (`sections`) 
    INNER JOIN `pages` ON pages.parent_id = sections.parent_id
    WHERE sections.parent_id = '1'
    ORDER BY sections.id ASC
    This works but I get duplicates..Currently there are 3 pages, I seem to get a duplicate section for each page..
    I have also tried LEFT JOIN, etc..

    Thanks..any help is appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,269
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you please show a few sample rows that are related from each table

    and then please show the result rows you expect from them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sections:

    HTML Code:
    id  |  parent_id    | page_id    | type     | title	
    61  |     1 	    |    1 	 | normal   | Section 3
    60  |	  1 	    |    1 	 | normal   | Section 2
    59  |	  1 	    |    3 	 | normal   | Section 1
    Pages:
    HTML Code:
    id  |  parent_id    | page_number  |
    1   |     1 	    |    1 	 
    3   |	  1 	    |    2 	 
    11  |	  1 	    |    4 	
    What I want is a list like this:
    HTML Code:
    id |  title    | parent_id  | page_number | 
    59 | Section 1 |   1        |        2

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Basically grab everything from the sections table and the page_number from the pages table that corresponds to the page_id of that section.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,269
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    your sample data did not make sense, so i'm going to go with this: "grab everything from the sections table and the page_number from the pages table that corresponds to the page_id of that section"

    Code:
    SELECT s.id
         , s.parent_id
         , s.page_id
         , s.type 
         , s.title
         , p.page_number 
      FROM sections AS s
    LEFT OUTER
      JOIN pages AS p
        ON p.id = s.page_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    [QUOTE=r937;3780075]your sample data did not make sense, so i'm going to go with this: "grab everything from the sections table and the page_number from the pages table that corresponds to the page_id of that section"

    Code:
    SELECT s.id
         , s.parent_id
         , s.page_id
         , s.type 
         , s.title
         , p.page_number 
      FROM sections AS s
    LEFT OUTER
      JOIN pages AS p
        ON p.id = s.page_id
    [/QUOTE
    r937 if I may, could you explain why you said it makes no sense? Thanks, I'll try out your example.
    Thanks again.

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I dont think that will work. I need to join them on the parent_id:

    This is what I have.
    Code:
    Letter(Parent)
      - Page
         -Sections
    So each letter has pages, and each page has sections. On all other queries, I grab all pages for each letter, and then all sections for each page. However on this, I simply need to return all sections for the Letter and their corresponding page_number. The page number is determined by the page_id. Thanks.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,269
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by virtualdevl View Post
    So each letter has pages, and each page has sections. On all other queries, I grab all pages for each letter, and then all sections for each page. However on this, I simply need to return all sections for the Letter and their corresponding page_number. The page number is determined by the page_id. Thanks.
    i don't understand how this relates to your sample data

    maybe you understand how it's supposed to work, but i don't

    where's the letter table?

    and what's the difference between a page id and a page number? and which one has parents? or do they both?

    totally lost
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i don't understand how this relates to your sample data

    maybe you understand how it's supposed to work, but i don't

    where's the letter table?

    and what's the difference between a page id and a page number? and which one has parents? or do they both?

    totally lost
    Ok, sorry. Lets see.

    Letter Table has: id, title
    Pages: page_id, letter_id, page_number
    Sections: id, letter_id, page_id, title, etc

    page_id is the pages.page_id. I thought I would need this to retrieve pages.page_number value for the section.

    So lets say the section belongs to page_number 1.

    Then in the sections table for this row, page_id would be the row in the pages table where the letter_id = the sections table letter_id and the pages.id = sections.page_id. I did this because there could be 100 pages in the pages table, but I only care about the pages that belong to this letter. Maybe my tables are not set up correctly? pages and sections have parents, which are the letters. Thanks for you help I really appreciate it!

  10. #10
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure if this makes any sense I have no clue how to design this stuff..but I downloaded something so I can illustrate the tables.. Sorry if its not technically correct.


  11. #11
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query in my first post seems to do all I want to do but I returns duplicates..I'm guessing this is because of the join. This is what I get back

    all the data here except for the page number comes from the sections table, only the page # comes from pages. Please let me know if you need any more info.


  12. #12
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow...r937 u were spot on, got it working with a tad of tweaking, thanks for the help, greatly appreciated!

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,269
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    thanks

    i am glad my confusion led to your successful resolution of the query

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

  14. #14
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Greatly appreciated!


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
  •