Databases
StackOverflow2013
Postsdbo
POmysql query join: Pages in Sections OR Subsections, is there a better alternative to GROUP BY to elim dupes?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql query join: Pages in Sections OR Subsections, is there a better alternative to GROUP BY to elim dupes?
    primarykey
    data
    text
    <p><strong>UPDATE: I am using the sql query shown in my question in production, but you are welcome to read the entire thread if you want to see an alternate approach to this, using sql with a UNION</strong></p> <p>I've experimented and made a result set to be used in a content search, but I want to make sure it's performance is the best it can be.</p> <p>I have a table named SECTIONS which holds 2 levels of sections, i.e. level 1 (a section) and level 2 (a subsection), in an Adjacency List model</p> <pre><code>SECTIONS: id, parent_id, name </code></pre> <p>I query that table twice to get columns in the arrangement</p> <pre><code>sec_id, sec_name, subsec_id, subsec_name </code></pre> <p>( this is so I can create uri links like /section_id/subsection_id )</p> <p>Now I join a separate table named PAGES where a page can be related to a section or a subsection (both not both) through the field section_id</p> <pre class="lang-sql prettyprint-override"><code>-- columns to return SELECT s.id as section_id, s.name as section_name, ss.id as subsection_id, ss.parent_id as subsection_parent_id, ss.name as subsection_name, p.section_id as page_section_id, p.name as page_name -- join SECTIONS into Sections and SubSections FROM ( select id, name from sections where parent_id=0 ) as s LEFT JOIN ( select id, parent_id, name from sections where parent_id!=0 ) as ss ON ss.parent_id = s.id -- now join to PAGES table JOIN ( select id, section_id, name from pages where active=1 ) as p ON ( p.section_id = s.id OR p.section_id = ss.id ) -- need to use GROUP BY to eliminate duplicate pages GROUP BY p.id </code></pre> <p>I get duplicate pages in the result set, so I use GROUP BY pages.id to remove the duplicates, but it degrades performance a little.</p> <p>Can you suggest a better way to eliminate duplicates?</p> <p>I've thought of creating a column in the SECTIONS join that holds the Section ID OR the Subsection ID (depending on the type of row - section or subsection), and then use that to relate to the PAGES section_id, so there would not be duplicate rows, but I can't figure out how to do it.</p> <p>Thanks</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSteve Wasiura
    1. USSteve Wasiura
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmysql query join: Pages in Sections OR Subsections, is there a better alternative to GROUP BY to elim dupes?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre you trying to retrieve pages that are in one given (section or sub-section)? In other words, your input is a (section or sub-section) ID, isn't it? I am sorry I am a bit confused as I do not see a `WHERE` clause in your query.
      singulars
      1. POmysql query join: Pages in Sections OR Subsections, is there a better alternative to GROUP BY to elim dupes?
      1. USRandomSeed
    2. COno criteria yet. i'm trying to build a set of rows from the PAGES table, whose contents can then be searched (when I add a criteria in later). I'm only assembling the columns I need right now for my final result set. thanks
      singulars
      1. POmysql query join: Pages in Sections OR Subsections, is there a better alternative to GROUP BY to elim dupes?
      1. USSteve Wasiura
    3. COI thought I could create a view from this to search against, but I just learned mysql doesn't like views with derived table in the FROM clause - Just-In-Time learning :(
      singulars
      1. POmysql query join: Pages in Sections OR Subsections, is there a better alternative to GROUP BY to elim dupes?
      1. USSteve Wasiura
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload