Re: [SQL] Optional join
"Slawek Jarosz" <[EMAIL PROTECTED]> wrote: > Hi, > I trying to write a query that will join 2 tables. Here's the > concept: > Table 1: table1, primary key pk1 > Table 2: table2, primary key pk2 > One of the fields (f2) in table2 contains either the primary key of > table1 or a NULL value. So normally a pretty basic query: > SELECT table1.*, table2.pk2 > FROM table1, table2 WHERE table2.f2 = table1.pk1; > BUT what I would like to do is show all records of Table 1 even if > there is no match in Table 2. Meaning that the reults could be > table1... table2.pk2 > table1... NULL > Doable? Yes. SELECT table1.*, table2.pk2 FROM table1 LEFT OUTER JOIN table2 ON (table1.pk1 = table2.pk2); HTH :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 Power over a man's subsistence is power over his will. Alexander Hamilton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inheritance or no inheritance, there is a question
Josh Berkus <[EMAIL PROTECTED]> wrote: > Vernon, > >> What is the best solution for this DB scheme problem? > > Have you considered not using inheritance? As a relational-SQL geek myself, > I'm not keen on inheritance -- I feel it mucks up the relational model. Not > everyone agrees with me, of course. > > Personally, I'd suggest the following structure: > > Profile A >id Primary Key >detail1 >detail2 > > Profile B >id Primary Key references Profile A ( ID ) >detail 3 >detail 4 >detail 5 > > Profile Languages >id not null references profile A ( ID ) >language id >primary key id, language id > > etc. > > In this way, Profile B is a child table with a 1:0-1 relationship > with Profile A. Multi-value dependancies, like Languages, can be > related to either the people who belong to the B group (and, by > implication, the B group) or the people who belong to the A group > only. > > Want the B group? SELECT A JOIN B > Want the A group only? SELECT A EXCEPT B > > This is the "relational" way to approach the problem. Grewvy! I've been running a system that takes various kinds of payments, some tables of which are below. INSERTs & UPDATEs only happen on the tables that inherit from the payment table. To sum up or otherwise do reports, I SELECT from the payment table. Is there some relational way to do this without ripping my hair out every time I want to do a new query? As some of you know, I don't have much hair left to lose ;) CREATE TABLE payment ( payment_id SERIAL NOT NULL PRIMARY KEY , order_id INTEGER NOT NULL REFERENCES order(order_id) ON DELETE RESTRICT , amount INTEGER NOT NULL -- pennies , payment_date DATE NOT NULL DEFAULT now() ); CREATE TABLE payment_check ( check_no INTEGER NOT NULL , payer_name VARCHAR(255) NOT NULL ) INHERITS (payment); CREATE TABLE payment_money_order ( issuer VARCHAR(255) NOT NULL , mo_num VARCHAR(64) NOT NULL ) INHERITS (payment); CREATE TABLE payment_wire ( payment_wire_desc VARCHAR(255) NOT NULL ) INHERITS (payment); Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 Fascism should more properly be called corporatism, since it is the merger of state and corporate power. Benito Mussolini ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] generating a sequence table against which to do a LEFT OUTER JOIN
Andrew Hammond <[EMAIL PROTECTED]> wrote:
> So I need an end result that has entries for all days, even when
> there's nothing happening on those days, generate from a timestamped
> event table. I've already got the interesting entries. Now I need to
> fill the holes.
>
> To do this, I'm thinking a LEFT OUTER JOIN against a date sequence
> table. So, how do I generate a table with every day from A to B?
>
> Or am I going about this the wrong way?
What you have is fine, but you're doing extra work. There's this neat
function in PostgreSQL 8.0 or better (you can write one for earlier
versions) called generate_series().
> SELECT pop_days('2005-01-01'::date, '2005-02-01'::date); -- barfs.
SELECT
'2005-01-01'::date + s.i * '1 day'::interval AS "Date",
t.your_date_col
FROM
generate_series(0,'2005-02-01'::date - '2005-01-01'::date - 1) AS s(i);
LEFT JOIN
your_table t
ON
('2005-01-01'::date + s.i = t.your_date_col);
You can also use generate_series() with a correllated subquery so as
not to have to hard-code dates.
HTH :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100mobile: +1 415 235 3778
When a man tells you that he got rich through hard work, ask him:
'Whose?'
Don Marquis, quoted in Edward Anthony, O Rare Don Marquis
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] JDBC Statement.setQueryTimeout : is there plan to implement this?
On Wed, Dec 14, 2011 at 10:01:37PM +0800, Craig Ringer wrote: > On 14/12/2011 9:29 PM, Sylvain Mougenot wrote: > >Even if the behaviour is not implemented, I guess it could be > >"mocked" if the call to Statement.setQueryTimeout(int seconds) > >generated the select statement "SELECT statement_timeout(SEC)". I > >know this is not ideal but could solve the problem temporarily. I > >could even be turned on by some driver settings. > > > > (following up on last post): See in particular this thread: > > http://archives.postgresql.org/pgsql-jdbc/2010-10/msg00071.php > > with posts like: > > http://archives.postgresql.org/pgsql-jdbc/2010-10/msg00131.php > http://archives.postgresql.org/pgsql-jdbc/2010-10/msg00077.php > > > I'm a little concerned about the proposal to use a java.util.Timer, > as IIRC there are issues with using a Timer in a JavaEE environment. > I'm struggling to find more than vague references like it being > "inappropriate for a managed environment" though. > > Ah, here: > > http://jcp.org/en/jsr/detail?id=236 > > "JavaTM Platform, Enterprise Edition (Java EE and formally known as > J2EETM) server containers such as the enterprise bean or web > component container do not allow using common Java SE concurrency > APIs such as java.util.concurrent.ThreadPoolExecutor, > java.lang.Thread, java.util.concurrent.ScheduledThreadPoolExecutor > or java.util.Timer directly." > > and > > "java.util.Timer, java.lang.Thread and the Java SE concurrency > utilities (JSR-166) in the java.util.concurrency package should > never be used within managed environments, as it creates threads > outside the purview of the container." > > I suspect that PgJDBC will have to get a timer from the container > via JNDI and fall back to direct instantiation if it is in a Java SE > environment. I'm not sure how to do that right now or whether it can > be done in a container-independent way (*shudder*). I'm quite sure > that using EJB timers is NOT the right way to do it - they're not > supported by web profile containers and are really intended for > "business level" timers that should be persistent across > redeploy/relaunch of appserver/reboot. > > I've CC'd David Fetter, the author of the JDBC patch. Do we have some ideas as to what strategies the Oracle and Microsoft SQL Server drivers do? As I recall, the MS-SQL Server ones used to use a Timer thread, although that may have changed. Are there other relevant drivers whose behavior we should look to? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
