query question

[Stijn Goris]

hi all,

I have a system where users can add comment to a picture (Gallery system).
When the users opens the webgalley he first sees the last comments made by
the users (the last 8). I could use a simple SELECT and ORDER BY on the
timestamp but when users would make 8 comments to a picture all the last
comments would be taken by that sole picture. My system should be somewhat
more intelligent and not give the last comments but give the last commented
pictures. So when 8 comments are made to one picture it would only appear
once in the last comment.

Via some INNER JOINS I query the pictureID ORDERED BY the timestamp (when
the comment was made). I though I 'd simply change the SELECT into a SELECT
DISTINCT but this doen't seem to work. I get a totally different result.
When I use SELECT I get
1704
1638
1637
1623
1623
1622
1621
1607

With SELECT DISTINCT this should be (I think)

1704
1638
1637
1623
1622
1621
1607
...

Instead I get a totally different result. Is this possible? If so, I
probably misunderstand the DISTINCT feature. I hope someone can help me out.

regards
Stijn

[Pieter Nobels]

Re: query question

Stijn Goris wrote:[color=blue]
> Instead I get a totally different result. Is this possible? If so, I
> probably misunderstand the DISTINCT feature. I hope someone can help me out.[/color]

I realy think it would be easyer to solve your problem when you would
post the results of the query with DISTINCT in it (and maybe also the
query).

Besides that, I think it's better for performance if you change a column
pictures.last_c omment every time someone posts a comment. The query will
be much easier (just SELECT ... FROM pictures ORDER BY last_comment DESC).

If you realy don't want this, you should check out OUTER JOINS.

--
Pieter Nobels

[Michael Austin]

Re: query question

Stijn Goris wrote:[color=blue]
> hi all,
>
> I have a system where users can add comment to a picture (Gallery system).
> When the users opens the webgalley he first sees the last comments made by
> the users (the last 8). I could use a simple SELECT and ORDER BY on the
> timestamp but when users would make 8 comments to a picture all the last
> comments would be taken by that sole picture. My system should be somewhat
> more intelligent and not give the last comments but give the last commented
> pictures. So when 8 comments are made to one picture it would only appear
> once in the last comment.
>
> Via some INNER JOINS I query the pictureID ORDERED BY the timestamp (when
> the comment was made). I though I 'd simply change the SELECT into a SELECT
> DISTINCT but this doen't seem to work. I get a totally different result.
> When I use SELECT I get
> 1704
> 1638
> 1637
> 1623
> 1623
> 1622
> 1621
> 1607
>
> With SELECT DISTINCT this should be (I think)
>
> 1704
> 1638
> 1637
> 1623
> 1622
> 1621
> 1607
> ...
>
> Instead I get a totally different result. Is this possible? If so, I
> probably misunderstand the DISTINCT feature. I hope someone can help me out.
>
> regards
> Stijn
>
>[/color]

the DISTINCT will apply to ALL of the columns listed in the SELECT statement for
example:

SELECT DISTINCT A.ID , B.comment, B.user from ....

applies to the entire ID+comment+user and since each is unique, they are
distinct - even if you have the same user.

Since MySQL 4.xx does not have derrived tables ie:
select x,y,z from (select a,b,c from x where blah blah)

you sort of have to do this in 2 steps

select distinct user from comments limit 8 ;

select b.comment, b.comment_date from a a, b b where a.id = b.id and b.user in
(user1,user2,us er3...) order by comment_date desc;

since I don't have your table layout or the actual query, the exact syntax will
be yours to work out...


--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)