Selecting Distinct rows

[y_oda2002]

Hi everyone,

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

Here is the query

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

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

[Dr John]

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.

[longneck]

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:

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

[y_oda2002]

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

[r937]

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)

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

[guelphdad]

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.