SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query help please!

    I've got the following MySQL tables:

    Code:
    section
      id
      title
      page_id
    
    page
      id
      title
      path
    
    subscription
      id
      section_id
    What I want to build is an SQL query that will return all distinct section_id values from the subscription table, along with each section's title, and the title/URL of the page in which each section resides.

    I'm sure this has got to be possible with a single query, but I'm having trouble working out exactly what the syntax is.

    Can someone help please? Cheers!

    EDIT: Nevermind, got it in the end:

    Code sql:
    SELECT s.section_id AS section_id
      , se.title AS section_title
      , p.title AS page_title
      , p.path AS page_url
    FROM (subscription s)
      INNER JOIN SECTION se ON s.section_id = se.id
      INNER JOIN page p ON se.page_id = p.id
    GROUP BY s.section_id

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select sub.section_id, p.url,p.title from subscription sub left join
    section s on sub.section_id = s.id left join page p on
    s.page_id = p.id group by sub.section_id

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    SJH, your GROUP BY is unnecessary

    aktar, you've got the wrong columns in the SELECT clause, and the query should be using INNER JOINs instead of LEFT OUTER (because it is unlikely that there is a subscription linking to a section that doesn't exist, or a section linking to a page that doesn't exist)
    rudy.ca | @rudydotca
    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
  •