PostgreSQL, merge join results I have 2 tables with common columns and would like to know if it's possible to request this 2 tables by merging the results.

Table "job" : parent_id, target_id, start_date, end_date, jobposition_id

Table "study" : parent_id, target_id, start_date, end_date

At this moment if I do something like :

SELECT *
FROM user AS u 
LEFT JOIN study AS s ON s.parent_id=u.id OR s.target_id=u.id 
LEFT JOIN job AS j ON j.parent_id=u.id OR j.target_id=u.id
WHERE u.id=2;


I got a resultset with every common column doubled (I have 2 times parent_id, target_id, start_date, end_date).

How can I get a simple resultset with only one column parent_id, etc (and a jobposition_id column set to NULL for every study result) ?

Thanks
Your query uses SELECT * ... which will select every column from the tables (in the order that they exist within the tables).  So if you have:

CREATE TABLE t1 (
  id bigserial primary key, -- bleh - never use just "id" .. this is only for example
  desc text
);

CREATE TABLE t2 (
  id bigserial primary key,
  t1_id bigint references t1 (id),
  desc text
);

select
  *
from t1
join t2 on t1.id = t2.t1_id


The query will give you the following columns in your result set:

id, desc, id, t1_id, desc


The simple solution is to basically NEVER use "*" ... always SPECIFY which columns you want .. and then you can also ALIAS those columns..

select
  t1.id AS t1_id,
  t1.desc AS t1_desc,
  t2.id AS t2_id,
  t2.desc AS t2_desc
from t1
join t2 on t1.id = t2.t1_id;


This query will return the following column names

t1_id, t1_desc, t2_id, t2_desc


One major problem with using "*" in your queries is that if the table structure ever changes, it will affect every single query that is using that table, which will often times break code.  Where as if you are specifying the column names, the chances of that happening are significantly reduced.  The resulting error messages that the database will give you are also a lot easier to understand when it comes to "what broke?"