Re: [GENERAL] LEFT JOIN, entry can not be referenced

"David G. Johnston" writes: > On Mon, Jun 26, 2017 at 5:31 PM, Jan Danielsson > wrote: >> SELECT >> wl.ts,wa.name,wl.user_id,u.name,wl.doc_id,d.doc_id,wl. >> docrev_id,dr.docrev,wl.file_id,f.fname,wl.issue >> FROM worklogs AS wl, workactions AS wa, users AS u >> LEFT JOIN documents AS d ON wl.do

Re: [GENERAL] LEFT JOIN, entry can not be referenced

On Mon, Jun 26, 2017 at 5:31 PM, Jan Danielsson wrote: > SELECT > wl.ts,wa.name,wl.user_id,u.name,wl.doc_id,d.doc_id,wl. > docrev_id,dr.docrev,wl.file_id,f.fname,wl.issue > FROM worklogs AS wl, workactions AS wa, users AS u > LEFT JOIN documents AS d ON wl.doc_id=d.id > LEFT JOIN docrevs AS dr ON

[GENERAL] LEFT JOIN, entry can not be referenced

Hello, I'm trying to use LEFT JOIN's in a manner which I imagine is pretty archetypal. In short; I have a table called worklogs which has a few columns that can't be NULL, and a few columns which may reference other tables or will be NULL. If the optional columns are not NULL I want to use th

Re: [GENERAL] Left join help

Hello Adrian, The way John taught me this query with CTE and then how that can be using with JOIN really helped me to understand. The query below is from John and it worked. SELECT * FROM missions LEFT JOIN submissions INNER JOIN members ON submissions.member_id = members.id A

Re: [GENERAL] Left join help

On 06/24/2017 08:01 AM, Arup Rakshit wrote: Hi, Thanks everyone for taking time to explain this. I tried to add a case statement and getting errors. Can you tell me how should I add a column to mark which mission is completed and which is not. My try is not working. Instead of the new CASE ex

Re: [GENERAL] Left join help

Hi, Thanks everyone for taking time to explain this. I tried to add a case statement and getting errors. Can you tell me how should I add a column to mark which mission is completed and which is not. My try is not working. Instead of the new CASE expression, the query works as expected. SELECT

Re: [GENERAL] Left join help

On Fri, Jun 23, 2017 at 3:17 PM, Arup Rakshit wrote: > Hi David, > > Can you tell me how can I add ordering between LEFT and INNER JOIN. I > think also that is where I am wrong, but not sure how to correct the > ordering. > ​Generally... FROM tbl1 LEFT JOIN (tbl2 JOIN ​tbl3 ON ...) tbl2_3 ON ..

Re: [GENERAL] Left join help

I tried a query, but it is not giving me any result. Can anyone help me pls? SELECT missions.*, CASE WHEN submissions.id IS NULL THEN 'incompleted' ELSE 'completed' END AS mission_status FROM "missions" LEFT JOIN submissions ON submissions.mission_id = missions.id INNER JOIN members O

Re: [GENERAL] Left join help

Hi David, Can you tell me how can I add ordering between LEFT and INNER JOIN. I think also that is where I am wrong, but not sure how to correct the ordering. Thanks, A On Jun 24, 2017, at 3:18 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Friday, June 23, 2017, Arup Ra

Re: [GENERAL] Left join help

On Friday, June 23, 2017, Arup Rakshit wrote: > > FROM "missions" LEFT JOIN submissions ON submissions.mission_id = > missions.id > INNER JOIN members ON members.id = submissions.member_id > Quick look here but having submissions as part of the inner join with members defeats the attempt at left

[GENERAL] Left join help

Hi, I have this relationship Track has many missions. Missions has many submissions. Each Submission has one member and one mission. Say I have track id 7. I want to find out which missions under track 7 are submitted by John ( a user id say 3 ) and which are not yet. I tried a query, but it is

Re: [GENERAL] Left Join with Limit 1

Running the queries individually and using a limit on the golite ip db results are back immediately 1-2ms but when using the first query it takes 2-3 seconds. Is there a way to use a limit in the join? This sounds like the real issue is a missing/incorrect index, but if you're on 9.4+ you can

Re: [GENERAL] Left Join with Limit 1

On 10/12/15 10:03 AM, Alex Magnum wrote: Is there a way to use a limit in the join? SELECT ... FROM table_a a LEFT JOIN ( SELECT ... FROM table_b WHERE ... LIMIT 1) b ON a.blah = b.blah -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture

[GENERAL] Left Join with Limit 1

Hello, I am trying to extract ip addresses from golite by joining two tables as posted below. Running the queries individually and using a limit on the golite ip db results are back immediately 1-2ms but when using the first query it takes 2-3 seconds. Is there a way to use a limit in the join?

Re: [GENERAL] left join with OR optimization

Sim Zacks writes: > I've seen written that a b-tree index can't be used on a join with an > OR. That's not the case ... > Is there a way to optimize a join so that it can use an index for a > query such as: > select > a.partid,a.duedate,coalesce(a.quantity,0)+sum(coalesce(b.quantity,0)) > from

Re: [GENERAL] left join with OR optimization

What version of PostgreSQL? On Jan 24, 2012, at 9:28, Sim Zacks wrote: > I've seen written that a b-tree index can't be used on a join with an > OR. Is there a way to optimize a join so that it can use an index for a > query such as: > > select > a.partid,a.duedate,coalesce(a.quantity,0)+sum(co

[GENERAL] left join with OR optimization

I've seen written that a b-tree index can't be used on a join with an OR. Is there a way to optimize a join so that it can use an index for a query such as: select a.partid,a.duedate,coalesce(a.quantity,0)+sum(coalesce(b.quantity,0)) from stat_allocated_components a left join stat_allocated_compon

Re: [GENERAL] left join count

On 12 February 2010 10:28, Richard Huxton wrote: > On 11/02/10 22:53, Greenhorn wrote: >> >> But I seem to be getting (after the join) the sum of amount x count of >> notes.  Can someone enlighten me with this problem? > >> select >>   energy_accounts_id, count(note) >>   ,sum(case when t.fin_year

Re: [GENERAL] left join count

On 11/02/10 22:53, Greenhorn wrote: But I seem to be getting (after the join) the sum of amount x count of notes. Can someone enlighten me with this problem? select energy_accounts_id, count(note) ,sum(case when t.fin_year = 2010 and t.fin_quarter = 1 then t.total_amount else 0 e

[GENERAL] left join count

Hi All, I'm trying to retrieve the count of notes associated for each transactions for table energy_transactions. But I seem to be getting (after the join) the sum of amount x count of notes. Can someone enlighten me with this problem? Here's the query that I am working with. select energy_acco

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

Hi, Le lundi 19 janvier 2009, Tom Lane a écrit : > But having said that: 8.4 will provide a standard trigger that > short-circuits vacuous updates, which you can apply to tables in which > you think vacuous updates are likely. It's your responsibility to place > the trigger so that it doesn't int

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

the only difference here is, that the trigger will memcmp (compare) all data. Say, if we have two columns, int and bytea, and just want to compare fist one - it will use a lot of cpu in vain. I have to say, it is a shame sometimes - that trigger isn't aware of what fields we do update exactly --

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

On Sun, 18 Jan 2009 22:12:07 +0100 Ivan Sergio Borgonovo wrote: > I've to apply a discounts to products. > > For each promotion I've a query that select a list of products and > should apply a discount. > > Queries may have intersections, in these intersections the highest > discount should be

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

On Mon, Jan 19, 2009 at 09:48, Grzegorz Jaśkiewicz wrote: > On Mon, Jan 19, 2009 at 4:43 PM, Tom Lane wrote: >> But having said that: 8.4 will provide a standard trigger that >> short-circuits vacuous updates, which you can apply to tables in which >> you think vacuous updates are likely. It's y

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

On Mon, Jan 19, 2009 at 4:43 PM, Tom Lane wrote: > But having said that: 8.4 will provide a standard trigger that > short-circuits vacuous updates, which you can apply to tables in which > you think vacuous updates are likely. It's your responsibility to place > the trigger so that it doesn't int

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

"Scott Marlowe" writes: > But seriously, it's doing what you told it to do. There might be > corner cases where you need a trigger to fire for a row on change, and > short-circuiting could cause things to fail in unexpected ways. The other argument against doing this by default is that with non-s

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

On Sun, 18 Jan 2009 19:44:40 -0700 "Scott Marlowe" wrote: > You could update returning rowsupdated, so you could run that and > get a list of all the rows that were updated. Then build a simple > select where not in (those rows) to get the rest for inserting. uh nice addition. I didn't check al

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

2009/1/19 Scott Marlowe : > Yes, but what about a table with an update trigger on it that does > some interesting bit of housekeeping when rows are updated? exactly, that's another one of reasons why I wouldn't write that patch :P > It's a prime example of fixing a problem created by not knowing

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

On Mon, Jan 19, 2009 at 12:53 AM, Grzegorz Jaśkiewicz wrote: > 2009/1/19 Scott Marlowe : >> Submit a patch. :) >> >> But seriously, it's doing what you told it to do. There might be >> corner cases where you need a trigger to fire for a row on change, and >> short-circuiting could cause things to

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

2009/1/19 Scott Marlowe : > Submit a patch. :) > > But seriously, it's doing what you told it to do. There might be > corner cases where you need a trigger to fire for a row on change, and > short-circuiting could cause things to fail in unexpected ways. as far as my little knowledge about pg goes

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

On Mon, Jan 19, 2009 at 12:12 AM, Grzegorz Jaśkiewicz wrote: > On Mon, Jan 19, 2009 at 2:44 AM, Scott Marlowe > wrote: >> Watch out for bloat when doing this. A simple where change of >> >> update table set b = 45 ; >> >> to >> >> update table set b = 45 where b <> 45 ; >> >> can save the db a

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

On Mon, Jan 19, 2009 at 2:44 AM, Scott Marlowe wrote: > Watch out for bloat when doing this. A simple where change of > > update table set b = 45 ; > > to > > update table set b = 45 where b <> 45 ; > > can save the db a lot of work, and if you can apply the same logic to > your update to save so

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

On Sun, Jan 18, 2009 at 2:12 PM, Ivan Sergio Borgonovo wrote: > I've to apply a discounts to products. > > For each promotion I've a query that select a list of products and > should apply a discount. > > Queries may have intersections, in these intersections the highest > discount should be appli

Re: [GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

Jan 2009 22:12:07 +0100 > From: m...@webthatworks.it > To: pgsql-general@postgresql.org > Subject: [GENERAL] left join with smaller table or index on (XXX is not null) > to avoid upsert > > I've to apply a discounts to products. > > For each promotion I've a que

[GENERAL] left join with smaller table or index on (XXX is not null) to avoid upsert

I've to apply a discounts to products. For each promotion I've a query that select a list of products and should apply a discount. Queries may have intersections, in these intersections the highest discount should be applied. Since queries may be slow I decided to proxy the discount this way: c

Re: [GENERAL] left join conditon causes error

2008/10/8 Andrus <[EMAIL PROTECTED]>: > Query Query deleted for brevity > ERROR: invalid reference to FROM-clause entry for table "destkonto" > LINE 7: ... =sihrkurs.kuupaev AND sihrkurs.raha=destkonto >^ > HINT: There

[GENERAL] left join conditon causes error

Query SELECT bilkaib.* FROM prpalk, (SELECT TRUE AS db, 1 AS sign UNION SELECT FALSE,-1 ) role,konto destkonto,bilkaib LEFT JOIN kurss sihrkurs ON CASE WHEN '00' IN(cr,db) THEN bilkaib.kuupaev-1 ELSE bilkaib.kuupaev END =sihrkurs.kuupaev AND sihrkurs.raha=destkonto.rah

Re: [GENERAL] LEFT JOIN issue

Just got an email saying that this has been fixed in 8.3.4, so I suppose I'll watch and wait for that. Thanks for looking, Alan (and anyone else who was watching this...) Dave On Tue, Sep 9, 2008 at 4:57 PM, Alan Hodgson <[EMAIL PROTECTED]> wrote: > On Tuesday 09 September 2008, "David Jaquay"

Re: [GENERAL] LEFT JOIN issue

On Tuesday 09 September 2008, "David Jaquay" <[EMAIL PROTECTED]> wrote: > I'm seeing a problem with a LEFT JOIN in 8.3.3, running on what I > believe to be an Ubuntu Heron server. The sql below demonstrates the > issue. explain doesn't execute the query. Show the output of the actual select - it

[GENERAL] LEFT JOIN issue

I'm seeing a problem with a LEFT JOIN in 8.3.3, running on what I believe to be an Ubuntu Heron server. The sql below demonstrates the issue. What I expect to see is no rows in the output, i.e. the LEFT JOIN should pair the two rows together, and the WHERE clause should decide that the joined row

Re: [GENERAL] left join a parenthesised inner join group

My bad. It is doing what it is supposed to. Now I just have to figure out why it is not giving me the results I am looking for. Sim "Sim Zacks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I tried the following code and was very suprised this didn't work the way I > had expected

[GENERAL] left join a parenthesised inner join group

I tried the following code and was very suprised this didn't work the way I had expected it to. Is my expectation correct or the way that it implements it correct? select * from a left join ( b join c on b.f1=c.f1 join d on d.f2=c.f2 join e on e.f3=d.f3) on a.f4=b.f4 I would like it to have all t

[GENERAL] Left join pa on dal resulting in null

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

Re: [GENERAL] Left join

> I tried to use "left join" to select data from my > database. Result is come out, but I didn't see the > value of "displayString". I added "left outer join", > same result. sounds like deeviceTypesEnum.enumID don't correspond to devices.productType. Jo > > SELECT devices.ProductType, device

[GENERAL] Left join

Hi, all, I tried to use "left join" to select data from my database. Result is come out, but I didn't see the value of "displayString". I added "left outer join", same result. SELECT devices.ProductType, devices.deviceKey, devices.fullDNSName, deviceTypesEnum.displayString, deviceTypesEnum.enu