Обсуждение: 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 = 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
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 > >
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 > > > >
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: > 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 >>> >>>
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 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 = 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 > > > > > > > > ---------------------------(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. 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 > >>> > >>> >
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: > 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 >>>>> >>>>> >>
> 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.