Building a "complex" select?
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.
Is this possible?
Regards,
BTJ
--
-----------------------------------------------------------------------------------------------
Bj�rn T Johansen
btj@havleik.no
-----------------------------------------------------------------------------------------------
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange Satanic messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"
-----------------------------------------------------------------------------------------------
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 = 22The 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
Nope... It returns 0 rows when there are no rows in log_stop with stoptype = 1...
BTJ
Scott Marlowe wrote:
Show quoted text
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 = 22The 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
Then maybe:
AND (log_stop.stoptype=1 OR log_stop.stoptype IS NULL)
??
Show quoted text
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 = 22The 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
I there a way to specify that I want the contribs directory (and its
children) compiled and installed during the build process in the
various BSD ports systems? Of course I understand that what works on
one BSD may not work on the others, but I don't see any FLAVORs or
make options that would suggest there is a way to do this (I have
looked in Open|Free ).
Also as a minor complaint, I understand that the contribs aren't
included in the default build for various reasons, but it would be
nice to have some more information about them included in the
documentation. I would expect some mention of them at least one of
the postgresql-8.0.2/ README, INSTALL, configure, Makefile files, but
there doesn't seem to be any.
For example, searching the online docs for "contribs" "contribs
-Release" or one of their members (ie "dbsize") comes up with release
notes about how contribs/foo was updated or fixed, or how it might be
used for testing something. I couldn't find any information online
regarding how to build/install them short of downloading the source,
unpacking, and reading the README files in the contrib directory. I
can do this without too much trouble, but I expect that many people
might not know where to look.
Just a little nitpicking, but I figure if they're included in the
release tarballs, there should probably be some more information on
how to install them. Otherwise it's just wasted space :)
Matt
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:
Show quoted text
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 = 22The 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
If that doesn't work, you'll need a subselect:
select * from
(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) as a
were a.stoptype=1 or a.stoptype IS NULL
Show quoted text
On Mon, 2005-04-18 at 11:58, 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 = 22The 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---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
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.
Show quoted text
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 = 22The 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
En un mensaje anterior, Matt Van Mater escribi�:
I there a way to specify that I want the contribs directory (and its
children) compiled and installed during the build process in the
various BSD ports systems? Of course I understand that what works on
one BSD may not work on the others, but I don't see any FLAVORs or
make options that would suggest there is a way to do this (I have
looked in Open|Free ).
cd /usr/ports/databases/postgresql-contrib
make && make install && make clean
Regards.
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:
Show quoted text
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 = 22The 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
cd /usr/ports/databases/postgresql-contrib
make && make install && make clean
doh! Yet another obvious answer that escaped me, thanks. It is worth
noting however, that this only applies to FreeBSD I don't see anything
like it in OpenBSD. Free has a more comprehensive ports collection
than Open though, so that's not a huge surprise. I don't have a
NetBSD box to check for comparison.
Despite me being blind, I think my other comment still holds true.