- 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
al Message -
From: "Louis-David Mitterrand"
To:
Sent: Friday, February 12, 2010 11:14 AM
Subject: [SQL] "left join" not working?
Hi,
This query:
select c.id_currency,max(p.modified_on) from currency c left join
price_line p using (id_currency) where p.id_line=1 group by
c.
Hi,
This query:
select c.id_currency,max(p.modified_on) from currency c left join
price_line p using (id_currency) where p.id_line=1 group by
c.id_currency;
doesn't list all c.id_currency's, only those with a price_line. However
this one does:
select c.id_currency,max(p.modified_on) from curren
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
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
Thanks,
--
Lu Ying
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql
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
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.LastModifiedBy,
changelog.LastModified
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
Hello all,
Ive been racking my brain for a few hours now and need some help, please!!!
I have the following tables :
account
=account_id
=account_type_id
=account_username
vendor
=account_id
=vendor_status
contact_name
=account_id
=name
=domain_type_id
contact_address
=account_id
=address
=d
It´s just want I need! Perfect! Thanks!
Carlos
> -Mensagem original-
> De: Richard Broersma Jr [mailto:[EMAIL PROTECTED]
> Enviada em: sábado, 1 de julho de 2006 18:45
> Para: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
> Assunto: Re: [SQL] Left join?
>
>
>
> 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
Hi,
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).
postgres=# sel
Greetings,
I have the following join, and in this join I am getting all of the
data except that which joins the pa table to the dao table. When I
try to join it using a left join, i get all of the data except this
table. When I use a right join, I get nothing at all. If I make
this a s
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
Hi,
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?
The two tables:
***
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
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 a typical value, and the
other one refers to an exception that applies for that tuple.
Each key ref
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
Hi.
Exists too tables (PostgreSQL 7.2.3):
a ("cl" integer primary key, a1, a2, ...) - 10 records;
b ("cl" integer primary key, b1, b2, ...) - 800 records.
BEGIN;
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";
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
Hi all,
I have three tables that are related in the following way:
'
| epr_ord_grupo < 1 - n > epr_ord_profesor < 0 - n > epr_ord_horario
`
In average there is 1.0407716514 rows in epr_ord_horario for each row
in e
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
>
Morning ...
I'm trying to wrack my brain over something here, and no matter
how I try and look at it, I'm drawing a blank ...
I have two tables that are dependent on each other:
notes (86736 tuples) and note_links (173473 tuples)
The relationship is that one no
"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
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 field filled in. As such, I need to
left
I am trying to output news. An article can have an image or not sometimes.
Can someone help me get this to work in Postgres?
select a.article_id, a.title, a.url, a.synopsis, a.publish_date,
c.parent_category_id, c.category_id, c.category_name,
i.server_image_name
from ((article a JOIN articl
hi all,
I try
select purc.trn_no,purcsub.item_no,purcsub.qty,emp_no,emp_na
from purc left join purcsub on (purc.trn_no=purcsub.trn_no)
left join emp on (purc.emp_no = emp.emp_no)
postgresql not run,please help,thank
Antti Linno <[EMAIL PROTECTED]> writes:
>> I think, the following select will solve your problem
>>
>> select first.id
>> from first
>> except
>> second.id_first
>> from second;
>>
> Nay, I got parse error.
Should be ... EXCEPT SELECT second.id_first ...
Anyway, we should have full ANSI join
Try this:
> Nay, I got parse error.
> Antti
select first.id
from first
except
select second.id_first
from second;
Greetings,
Mikolaj.
Antti Linno wrote:
>
> > I think, the following select will solve your problem
> >
> > select first.id
> > from first
> > except
> > second.id_first
> > from second;
> >
> Nay, I got parse error.
'select' is missing after 'except'.
select first.id
from first
except
select second.id_firs
> I think, the following select will solve your problem
>
> select first.id
> from first
> except
> second.id_first
> from second;
>
Nay, I got parse error.
Antti
Antti Linno wrote:
> Greetings.
> I have a problem. I have 2 tables. E.g. work and workers. I want to
> select records, that 1st table has, and the second hasn't(both have id
> attribute). I mean I can't do it with is NULL, because those records don't
> exist. I was shown, how it is done with my
Greetings.
I have a problem. I have 2 tables. E.g. work and workers. I want to
select records, that 1st table has, and the second hasn't(both have id
attribute). I mean I can't do it with is NULL, because those records don't
exist. I was shown, how it is done with mysql
select first.id,second.id
47 matches
Mail list logo
