[Wolves] mysql select help

[Chris Ellis]

Hi Wayne

On 13 March 2012 01:28, Wayne Morris <waynelists at machx.co.uk> wrote:
> On 12/03/2012 16:56, Mark Rogers wrote:
>>
>>
>>
>> .. should get you the data you want.
>>
>> I haven't tested that!
>>
> You're a star! A bit of playing got:
> "SELECT * FROM phonelist WHERE trydate = (SELECT trydate FROM phonelist
> WHERE trydate > DATE(NOW()) ORDER BY trydate ASC LIMIT 1 )" ;
>
> so as I understand it the brackets return the (1) date that next occurs
> after 'NOW', the outside the brackets selects all the results on the day.

The brackets define what is known as a subquery.  This is a query
which will be executed prior to the
main query, the output of the subquery is then used in the main query
(the planner may optimise this).

Sub queries can return a single row, or a set of rows.  When used with
'=' operator, the sub query should
only return one result.  You can use the 'in' operator where the sub
query returns multiple rows.

You can also use an aggregate function in your subquery rather than a
order and limit, this should be more
efficient:

SELECT *
FROM phonelist
WHERE trydate = (
    SELECT MIN( trydate )
    FROM phonelist
    WHERE trydate > DATE(NOW())
)

Can you share your schema?  It makes it easier to assist in SQL
problems when you know what the table
structures are.

>
> cheers
>
> Wayne
>

Chris