SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-24-2008, 06:08 PM
| ||||
| ||||
 Conditional OR 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      |
|
04-24-2008, 06:08 PM
| |||
| |||
| Re: Conditional OR 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') |
|
04-24-2008, 06:08 PM
| |||
| |||
| Re: Conditional OR 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 Yes, and so would you if you read the manual to find out how IF should be used. In future, please do not post problem descriptions like "This croaks on me". In this case the error is obvious, however, if it hadn't been, then that description would have been as useful as a chocolate fireguard. If when you tried to execute this, you got an error message, then post it in its entirety. If you saw something you didn't expect, then tell us. We will be able to help you much more if you think about what is needed before posting. SELECT * FROM table WHERE Status = 'Live' OR Status = 'Ordered' OR (Status = 'InProgress' AND Start >= NOW() AND End >= Start) Note: I prefer using AND, YMMV. Note: don't you need a time machine to have End < Start? Note: your statement "What I need to do is retrieve the data that matches a status of InProgress, Live and Ordered." is not possible. A record can have only one of these statuses, not all 3. |
|
04-24-2008, 06:08 PM
| |||
| |||
| Re: Conditional OR 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. |
|
04-24-2008, 06:08 PM
| |||
| |||
| Re: Conditional OR Wow, push some of your buttons Cap'n? Thanks for your concern. JC On Apr 21, 11:28*am, Captain Paralytic <paul_laut...@yahoo.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 > > Yes, and so would you if you read the manual to find out how IF should > be used. > > In future, please do not post problem descriptions like "This croaks > on me". In this case the error is obvious, however, if it hadn't been, > then that description would have been as useful as a chocolate > fireguard. > > If when you tried to execute this, you got an error message, then post > it in its entirety. If you saw something you didn't expect, then tell > us. We *will be able to help you much more if you think about what is > needed before posting. > > SELECT * FROM table > WHERE Status = 'Live' > OR Status = 'Ordered' > OR (Status = 'InProgress' AND Start >= NOW() AND End >= Start) > > Note: I prefer using AND, YMMV. > Note: don't you need a time machine to have End < Start? > Note: your statement "What I need to do *is retrieve the data that > matches a status of InProgress, Live and Ordered." is not possible. A > record can have only one of these statuses, not all 3. |
|
04-24-2008, 06:08 PM
| |||
| |||
| Re: Conditional OR It DOES make sense. This was originally part of a much more vast query and I really appreciate the help. Thanks! JC On Apr 21, 11:29*am, "petethebl...@googlemail.com" <petethebl...@googlemail.com> wrote: > 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. |
|
04-24-2008, 06:09 PM
| ||||
| ||||
| Re: Conditional OR On Apr 21, 8:28 am, Captain Paralytic <paul_laut...@yahoo.com> wrote: > then that description would have been as useful as a chocolate > fireguard. > Hey, don't knock it if you haven't tried it! (I'm biased, I like *anything* in chocolate!) |
 |
« Is it worth doing the CMDEV 5.0 Exams at this point or should I waitfor 6.0?
|
mysql start program »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
All times are GMT. The time now is 11:14 AM.
