- Original Message -
From: "Louis-David Mitterrand"
To:
Sent: Friday, February 12, 2010 11:57 AM
Subject: Re: [SQL] "left join" not working?
On Fri, Feb 12, 2010 at 11:35:02AM -, Oliveiros C, wrote:
My first guess is that
NULL fails the condition on your WHERE clause
On Fri, Feb 12, 2010 at 11:35:02AM -, Oliveiros C, wrote:
> My first guess is that
> NULL fails the condition on your WHERE clause,
> p.id_line = 1
>
> So your WHERE clause introduces an additional level of filtering
> that filters out the NULLs coming from the LEFT JOIN...
So, if I understan
> where p.id_line=1
this filters rows after join was applied. Try this
select c.id_currency,max(p.modified_on) from currency c left join
price_line p using (id_currency) where (p.id_line=1 or p.id_line is null) group
by
c.id_currency;
> Hi,
> This query:
> select c.id_currency,max(p.modif
My first guess is that
NULL fails the condition on your WHERE clause,
p.id_line = 1
So your WHERE clause introduces an additional level of filtering that
filters out the NULLs coming from the LEFT JOIN...
Didn't do any tests, it's just a guess...
Best,
Oliveiros
- Original Message -
On May 22, 2009, at 1:51 PM, Emi Lu wrote:
Two tables, each contains more than hundreds of thousands records.
Is there any efficiency differences between (1) and (2)?
(1) T1 inner join T2 using (c1, c2)
(2) T1 left join T2 using (c1, c2) where c2 is not null
Yes, stick with the first. I
Thanks for the feedback, everyone.
> > FROM tasks
> > LEFT JOIN clients ON tasks.ClientId = clients.ClientId
> > LEFT JOIN iteminfo ON tasks.Id = iteminfo.ItemId
> >LEFT JOIN changelog ON tasks.Id = changelog.ItemId
> > LEFT JOIN ticklers ON tasks.Id = ticklers.RelatedId
> > WHERE
Ryan Wells wrote:
>
> FROM tasks
> LEFT JOIN clients ON tasks.ClientId = clients.ClientId
> LEFT JOIN iteminfo ON tasks.Id = iteminfo.ItemId
>LEFT JOIN changelog ON tasks.Id = changelog.ItemId
> LEFT JOIN ticklers ON tasks.Id = ticklers.RelatedId
> WHERE tasks.Id = '123456';
>
> (
On Tue, 2008-11-18 at 16:48 -0600, Ryan Wells wrote:
> Since it works, my question is really more about principles: Given
> that each of the tables in question will contain tens of thousands of
> rows, is a nested join really the best way to approach this?
I don't see what's wrong with it. The
On Nov 18, 2008, at 5:48 PM, Ryan Wells wrote:
While looking through our data layer code today, I ran across this
query:
SELECT
tasks.*,
clients.FirstName,
clients.LastName,
clients.MiddleInitial,
iteminfo.CreatedBy,
iteminfo.StationId,
iteminfo.CreatedDate,
changelog.LastModified,
changelog
Thanks codeWarrior - you got me 99% there - I just needed to add the NULL
"trick" on the join w/ the contact_phone and contact_address tables and that
got me the results I was after!
This is what I the final qry looks like :
SELECT
A.account_id,
A.account_username,
V.vendor_contract_signed_date,
On Thu, Jan 25, 2007 at 06:51:34PM -0500, Rob V wrote:
>
> I know I have to use a left join - but I can seem to figure out the syntax
> when dealing w/ different columns of the same table.
I haven't tested this to remind myself for sure that it will work,
but I think you ought to be able to RIGHT
Fisrt -- you probably want to start by doing fully qualified JOINS and then
you want to allow joins with nulls on the columns that are allowed to be
empty: I am doing this sort of off the top of my head ... but the thing you
need to do generally is to COMPLETELY QUALIFY all of your joins and the
Just 1 followup to this :
they MAY or MAYNOT have records in the following tables :
contact_address,contact_phone
There may also be multiple records in that table w/ the same account_id -
but the domain_type_id will be different.
so the contact_phone could have 2 records :
account_id 1, domain PR
> In the following table, codsol, codate and codfec are foreign keys
> referencing table func and I need some help to codify a SELECT command that
> produces the following result set but instead of codsol, codate and codfec I
> need the respectives names (column nome from table func).
>
> postgre
Manuel,
> I'm facing a wired problem. When I left join two tables PostgreSQL is
> able to do it fast using the corresponding indices, However, if I
> define a simple view (to format the data) on one of the tables, the
> left join does not use the indices. Is something wrong here?
At a guess, the
Yes, Bruno. Thank you very much. That's what I was looking for, but since
I hadn't used CASE nor COALESCE, I didn't know it was easier that way.
The idea is that on a column I have info about a routine task, and in
another one I have info about human-made changes to the time of that task,
like as
On Wed, Jan 28, 2004 at 20:27:00 -0800,
Octavio Alvarez <[EMAIL PROTECTED]> wrote:
>
> Hi. I have a table with two foreign keys (1 field each), like in
>
> id| serial
> ext_key_original | integer
> ext_key_exception | integer
>
> They mean different things, as one refers to
Alexey Dashevsky <[EMAIL PROTECTED]> writes:
> DECLARE "c" SCROLL CURSOR FOR select a.*,r.b1 as rb1 from a left join b using
> (cl) order by a.cl;
> FETCH FORWARD 100 in "c";
> FETCH FORWARD 100 in "c";
> MOVE -200 in "c";
> [ core dump ]
Some plan node types don't cope very well with being run b
Tomasz,
> select c.id1, d.id1, sum(
> case when c.id2 is null then 0.5 else c.val2 end *
> case when d.id2 is null then 0.5 else d.val2 end *T1.val1)
> from
> T1
> left join T2 c on (c.ID2 = T1.id)
> left join T2 d on (d.id2 = T1.id and (c.id1 < d.id1 or c.id1 is null))
> group by c.id1,
Manuel Sugawara <[EMAIL PROTECTED]> writes:
> Ouch, 3117.48 msec vs. 1.15 msec is a huge difference. I need
> something else? or may be postgres optimizer can't cope with
> left/right joins?
I think the problem is you're constraining the join order into a very
inefficient one. See
http://www.ca
Perfect, thank you ... i knew I was overlooking something obvious ... the
query just flies now ...
On Mon, 18 Jun 2001, Tom Lane wrote:
> The Hermit Hacker <[EMAIL PROTECTED]> writes:
> >> Try adding ... AND n.nid = 15748 ... to the WHERE.
>
> > n.nid is the note id ... nl.id is the contact id
The Hermit Hacker <[EMAIL PROTECTED]> writes:
>> Try adding ... AND n.nid = 15748 ... to the WHERE.
> n.nid is the note id ... nl.id is the contact id ...
Ooops, I misread "n.nid = nl.nid" as "n.nid = nl.id". Sorry for the
bogus advice.
Try rephrasing as
FROM (note_links nl JOIN notes n ON (
I think that using INNER JOIN between nl and n (on n.nid=nl.nid) or
joining those tables in a subquery might work.
On Mon, 18 Jun 2001, The Hermit Hacker wrote:
> Is there some way to write the above so that it evaluates:
>
> WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.ty
On Mon, 18 Jun 2001, Tom Lane wrote:
> The Hermit Hacker <[EMAIL PROTECTED]> writes:
> > FROM note_links nl, notes n LEFT JOIN calendar c ON (n.nid = c.nid)
> > WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
> > AND (nl.id = 15748 AND contact_lvl = 'c
The Hermit Hacker <[EMAIL PROTECTED]> writes:
> FROM note_links nl, notes n LEFT JOIN calendar c ON (n.nid = c.nid)
> WHERE (n.type = 'A' OR n.type = 'N' OR n.type = 'H' OR n.type = 'C')
> AND (nl.id = 15748 AND contact_lvl = 'company')
> AND n.nid = nl.nid
>
"Haywood J'Bleauxmie" wrote:
>I have a database that tracks work orders. Each order tracks two entries
>from the employees table; the employee ID of the person assigned to the
>work order and the ID of the person who completed the order. Each work
>order may have one, both, or neither f
26 matches
Mail list logo
