Re: Building a "complex" select?

Well, I thought this should indicate it: "And if there are 0 rows with stoptype = 1,
my select should return 1 row." but maybe I was a bit unclear...

The problem is that the log_stop table stores log values for x different kind of logs
separated with the stoptype field... And I am trying to design a report (a
master/detail type) where all the values from the order table should be shown with a
table listing the fields from log_stop beneath it...

BTJ

Scott Marlowe wrote:
> But that doesn't make a log of sense to me. And which row do you want?
> I'm thinking your data /layout /logic might need some rethinking, or at
> least re-stating. Your original post didn't seem to indicate this need.
>
>
> On Mon, 2005-04-18 at 12:05, Bjørn T Johansen wrote:
>
>>Nope, I have already tried that one... But this one returns 0 rows when eg. stoptype
>>= 2.. (it works ok when stoptype = 1 or null...) But I should get one row when
>>stoptype = 2 as well, but then with null values from fields in the log_stop table...
>>
>>I am not sure if this is possible, without writing a procedure or similar...
>>
>>BTJ
>>
>>Scott Marlowe wrote:
>>
>>>Then maybe:
>>>
>>>AND (log_stop.stoptype=1 OR log_stop.stoptype IS NULL)
>>>
>>>??
>>>
>>>On Mon, 2005-04-18 at 11:55, Bjørn T Johansen wrote:
>>>
>>>
>>>>Nope... It returns 0 rows when there are no rows in log_stop with stoptype = 1...
>>>>
>>>>BTJ
>>>>
>>>>Scott Marlowe wrote:
>>>>
>>>>
>>>>>On Mon, 2005-04-18 at 11:24, Bjørn T Johansen wrote:
>>>>>
>>>>>
>>>>>
>>>>>>I need a select like this..:
>>>>>>
>>>>>>select ordre.id, ordre.desc, log_stop.stoptype from ordre left outer join log_stop on
>>>>>>ordre.id = log_stop.ordreid where ordre.id = 22
>>>>>>
>>>>>>The problem is that I need to include "where log_stop.stoptype = 1". So if log_stop
>>>>>>includes 3 rows with stoptype = 1 and ordreid = 22, my select should return 3 rows.
>>>>>>Also, if log_stop includes 3 rows with stoptype = 1 and 2 rows with stoptype = 2, my
>>>>>>select should still return 3 rows. And if there are 0 rows with stoptype = 1, my
>>>>>>select should return 1 row.
>>>>>
>>>>>
>>>>>Would this work?
>>>>>
>>>>>select ordre.id, ordre.desc, log_stop.stoptype from ordre left outer join log_stop on
>>>>>ordre.id = log_stop.ordreid where ordre.id = 22 AND COALESCE(log_stop.stoptype,1)=1
>>>>>
>>>>>
>>