I am working on complicated problem, but I will simplify it to this problem.

I have two tables

A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]

and i want to update the third:

C [ID, column1, column2]

I am writing a query to

Select A.column1, B.column1 from A,B where  (A.column1 -B.column1)=1 limit 1;

Now I want to get all the points that

insert C(column1, column2)
Select A.column1, secElement from (Select A.column1 firstElement, B.column1 secElement from A,B where  (A.column3 -B.column3)=1 limit 1) as ex, A where A.column1=ex.firstElement limit 5;

But I am getting just one row.

I aspire to get a result like that.

  • Loop on A

  • for every element in A, I should find its peer.

But I couldn't find a way to do the condition. Take the element, look for its peer then go to the other element.

A.column1 | secElement

25033419 | 244959
25033420 | 244960
25033421 | 244961
25033422 | 244962
25033424 | 244963 (5 rows)

I can express it algorithmically :

Foreach (element e in A)   //Global Query 
  Find_Peer(e)             // Query 1

UPDATE: I'm not in syntax. I am trying to get adapted to it. but it should be something like that.

CREATE OR REPLACE FUNCTION PointPeer(elem integer) RETURNS text AS $$
Select A.column1, B.column1 from A,B where  (A.column1 -B.column1)=1 where  A.ID=elem limit 1::text
$$ LANGUAGE sql;

Select A.ID, PointPeer(A.ID) from A limit 5;

I still struggling with the Syntax.

  • Huh? Try to set this out step by step, explaining what your first query is supposed to be doing, what you define as an element's "peer", etc. Feb 13, 2014 at 0:50
  • In SQL, that's normally called a JOIN. Look up JOIN in a basic SQL book or web site training like w3school or something.
    – ETL
    Feb 13, 2014 at 2:21
  • No ! It's not Join. I need to work on something similar to this (linuxtopia.org/online_books/database_guides/…). Join give only one one record where we do the condition. Feb 13, 2014 at 2:37
  • Check the UPDATE Feb 13, 2014 at 2:43
  • Please remove some noise, only leave what's helping to define the problem. Will it be (A.column3 -B.column3) or (A.column1 -B.column1) as condition? 5 rows per elem in A or 5 rows per A.id or all peers? If just 5, how to pick? Please provide relevant table definitions including UNIQUE and NOT NULL constraints - what \d tbl gives you in psql. The syntax won't be a problem as soon as the job is defined in a way we can understand. Feb 13, 2014 at 14:39


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.