MySQL JOIN help

[virtualdevl]

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:

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.

[r937]

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

[virtualdevl]

Sections:

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:

id  |  parent_id    | page_number  |
1   |     1 	    |    1 	 
3   |	  1 	    |    2 	 
11  |	  1 	    |    4 	

What I want is a list like this:

id |  title    | parent_id  | page_number | 
59 | Section 1 |   1        |        2

[virtualdevl]

Basically grab everything from the sections table and the page_number from the pages table that corresponds to the page_id of that section.

[r937]

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"

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

[virtualdevl]

[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"

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.

[virtualdevl]

Ok, I dont think that will work. I need to join them on the parent_id:

This is what I have.

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.

[r937]

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

[virtualdevl]

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!

[virtualdevl]

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.

[virtualdevl]

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.

[virtualdevl]

Wow...r937 u were spot on, got it working with a tad of tweaking, thanks for the help, greatly appreciated!

[r937]

thanks

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

[virtualdevl]

Greatly appreciated!