Distinct help

PHP
1

Hello,
I thought I would ask in the PHP forum, if you know of a way to limit the first query in a LEFT JOIN to one record. This to help answer this question.
I’ve come up with this query.

$sql = "SELECT 
    b2.gebruiker_id,
    b2.bericht,
    b2.verzonden
    FROM berichten AS b1 
        LEFT JOIN berichten AS b2
            ON b2.gebruiker_id = b1.gebruiker_id  
    WHERE b1.isNieuw = 1";

However, it will return ALL records from the first query where isNieuw = 1.
SO we do want all matching results from the JOIN query but only one result from the first query so we can’t use LIMIT 1.
Any ideas?

It can be done in two looped queries.
It could be weeded out building a data array with the results.

Just trying to see if it can be done in a single query.

2

You could join a subquery and limit that subquery.


SELECT something
FROM some_table
LEFT JOIN
(SELECT something_else FROM some_other_table LIMIT 1)

(Don’t know if this works in MySQL, it does in PostgreSQL, and I’m guessing it does in MySQL as well)

3

But isn’t LIMIT applied across full query?
What we really need is the LIMIT applied to the first query then all matching records from the second.

4

r937 came up with the answer!!!

SELECT gebruiker_id
     , bericht
     , verzonden
  FROM berichten
 WHERE gebruiker_id =
       ( SELECT gebruiker_id
           FROM berichten
          WHERE verzonden =
                ( SELECT MIN(verzonden)
                    FROM berichten
                   WHERE isNieuw = 1 ) )
5

r937 indeed did in the mySQL forum and it makes me very very happy. Thanks a lot Rudy :tup:

@Drummin: You thanks a lot as well Drummin for all yout input and suggestions here and on the mySql forum

6

Not if you put it in a subquery, then it just limits the subquery.
This is basically what r937’s answer also does, except he uses an implicit limit using the fact that the MIN function will only return one value.