SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict y_oda2002's Avatar
    Join Date
    May 2005
    Posts
    219
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting Distinct rows

    Hi everyone,

    I have a mysql query which I am trying to select distinct information with.

    Here is the query
    Code:
    SELECT DISTINCT news.newsId, news.headline, news.posted, news.article, authors.authorName, authors.authorEmail, paper.paperDesc, photo.photoLevel, photo.photoType, photo.photoDesc FROM news, authorNews, authors, paperNews, paper, photo, sectionNews, sections WHERE news.newsId=photo.photoNewsId AND photo.photoLevel=1 AND news.newsId=authorNews.newsId AND authorNews.authorId=authors.authorId AND news.newsId=paperNews.newsId AND paperNews.paperId=paper.paperId AND news.newsId=sectionNews.newsId AND sectionNews.sectionId=1 AND (paper.paperId=1 OR paper.paperId=2)
    My problem is that I am not getting distinct newsId's. I have a feeling it is because the entire row is unique to the query. Is there a way I could make the query also only grab a newsId once.

    I.E.
    currently it pulls out this
    Code:
    ---------------------------------------
    |   News Id  |      Other Column Info |
    ---------------------------------------
    |      1        |         some info   |
    |      1        |         some info   |
    |      1        |         some info   |
    |      2        |         some info   |
    ---------------------------------------
    I want to be able to pull out one newsId=1 and pull out the newsId=2 also.
    Is this possible?
    I know I could loop through the fetching ignoring the non-unique rows but for efficiency I would like to only grab what I need.

    Thanks,

    Nick
    Nicholas Rhodes
    Asgard Development
    http://www.asgard-dev.com

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Test:
    Drop the DISTINCT
    Do you get the EXACT same data returned? Same no. of rows?

    If you have some one to many relations (and I suspect you do as some of your eight tables look as if they solve many to many relations) then you can generate a load of distinct rows from the query with or without the DISTINCT in place. DISTINCT means the entire row returned is unique, not the newsID alone.

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    please format your queries before posting them. putting them all on one line like that makes them very difficult to read. you wouldn't write your PHP all on one line, so why should you write your SQL like that?

    fixing your query is going to be a multi-step process, so bear with me.

    i have rewritten your query with explicit joins. what do you notice is missing? hint: the very last line is missing:
    Code:
    SELECT DISTINCT
           news.newsId
         , news.headline
         , news.posted
         , news.article
         , authors.authorName
         , authors.authorEmail
         , paper.paperDesc
         , photo.photoLevel
         , photo.photoType
         , photo.photoDesc
      FROM news
      JOIN authorNews
        ON news.newsId=authorNews.newsId
      JOIN authors
        ON authorNews.authorId=authors.authorId
      JOIN paperNews
        ON news.newsId=paperNews.newsId
      JOIN paper
        ON paperNews.paperId=paper.paperId
       AND paper.paperId=1 OR paper.paperId=2
      JOIN photo
        ON news.newsId=photo.photoNewsId
       AND photo.photoLevel=1
      JOIN sectionNews
        ON news.newsId=sectionNews.newsId
       AND sectionNews.sectionId=1
      JOIN sections

  4. #4
    SitePoint Addict y_oda2002's Avatar
    Join Date
    May 2005
    Posts
    219
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,

    Sorry about that longneck.
    In the php script there are a bunch of variables for building dynamic sql queries so I just echoed the query out and copied it into the post. Forgot about line breaks. I'll watch that next time.

    also, I am a beginner with mysql.
    I was taught to put the join information in the where clause.
    Is it more efficient to do it in the from clause?
    I notice it is easier to understand how the table is being joined.

    Thanks again,

    Nick
    Nicholas Rhodes
    Asgard Development
    http://www.asgard-dev.com

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by y_oda2002 View Post
    Is it more efficient to do it in the from clause?
    it's no less efficient

    (it could be more efficient, but the situations where it's more efficient to use JOIN syntax with ON clauses are too hard to explain right now)

    Quote Originally Posted by y_oda2002 View Post
    I notice it is easier to understand how the table is being joined.
    yes!!

    trust me, you will appreciate how important this is whenever you come to a multi-table join query that someone else wrote and you have to try to understand what it's doing

    note: that someone could be you, a few months down the road

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

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by y_oda2002 View Post
    also, I am a beginner with mysql.
    I was taught to put the join information in the where clause.
    Is it more efficient to do it in the from clause?
    I notice it is easier to understand how the table is being joined.
    two things to note:

    a) the mysql manual suffers from using this syntax so it is easy to see how folks fall into that trap

    b) it only works when you are using inner joins, when you move to outer joins, it might work, but it might also easily introduce errors

    when you do a left join but use a where clause you can inadvertantly change your outer to an inner join.

    how you ask?

    if you do an outer join but impose a condition on the right hand table in a where clause instead of in the ON clause, you change your outer to an inner join.


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
  •