On 21 Apr, 16:21, "petethebl...@googlemail.com"
<petethebl...@googlemail.com> wrote:
> On 21 Apr, 16:13, worldcycl...@gmail.com wrote:
>
>
>
> > I have a table with 4 fields, Name, Status, Start, End. Like below....
>
> > Name | Status | Start | End
> > Fred | InProgress | 05-31-2008 | 06-30-2008
> > Wilma | InProgress | 05-31-2008 | 06-30-2008
> > Barney | Live | 05-31-2008 | 06-30-2008
> > Betty | Ordered | 05-31-2008 | 06-30-2008
> > Dino | Dead | 05-31-2008 | 06-30-2008
>
> > etc... etc...
>
> > What I need to do is retrieve the data that matches a status of
> > InProgress, Live and Ordered.
>
> > This works ok of course..
> > SELECT * FROM table
> > WHERE Status = 'Live'
> > OR Status = 'Ordered'
> > OR Status = 'InProgress'
>
> > What I need to do is to modify the InProgress to only return if the
> > Start is >=NOW() and End is >= Start
>
> > I'm on MySQL 4. I have tried..
>
> > SELECT * FROM table
> > WHERE Status = 'Live'
> > OR Status = 'Ordered'
> > OR IF(Status = 'InProgress' && Start >= NOW() && End >= Start)
>
> > This croaks on me. Any ideas where this query might be wrong?
> > Many thanks in advance!
> > JC
>
> Try
> SELECT * FROM table
> WHERE Status = 'Live'
> OR Status = 'Ordered'
> OR (Status = 'InProgress' AND Start >= NOW() AND End >= Start)
>
> I think you can also use IN so your first query could have been...
> SELECT * FROM table
> WHERE Status IN( 'Live', 'Ordered', 'InProgress')

Just a quick extra thought. If you have a separate table like this....
tbl_progress_stages
uid,stage_name
1,New Order
2,Checked
3,Printed
4,Stock Ordered
5,In Stock
6,In Workshop
7,Made
8,Despatched

You can simply check the code e.g. WHERE progress_uid>3

Hope that makes sense.