0

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

  • 1
    What output would you expect for a user who has a matching row in both the study and the job table? – Andomar Jul 16 '15 at 17:03
  • @Andomar =>Got the solution with UNION (see validated answer). Thanks – Vincent Caggiari Jul 16 '15 at 17:23
1

I hope to have fully understood your request. With this query you get a concatenation of tables (a UNION) with all records.

SELECT parent_id, target_id, start_date, end_date, NULL AS jobposition_id FROM study
UNION ALL
SELECT parent_id, target_id, start_date, end_date, jobposition_id FROM job

You can play with UNION to obtain the merging concept you have in mind. Here's a link to the official documentation (PostgreSQL 9.4): UNION clause. I think that you would like to remove duplicates (if there are duplicate entries in both tables), so probably the UNION DISTINCT is right for you.

|improve this answer|||||
  • Just found the solution => stackoverflow.com/questions/4415109/… Union is exactly what I needed. Thank you. – Vincent Caggiari Jul 16 '15 at 17:21
  • Glad you found it ;) The query I proposed is the starting point for more complex queries that can account for duplicate entries over specific values (this is what you control with LEFT JOINs and not with UNIONs). UNION only requiers same domains (column names and data types). There are also other useful operators like INTERSECT and EXCEPT. – pietrop Jul 16 '15 at 17:25
1

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?"

|improve this answer|||||
  • Thank you for your answer. I need to merge the "t1_id" column with "t2_id". Same goes for t1_desc and t2_desc, etc. Any idea ? – Vincent Caggiari Jul 16 '15 at 16:09
  • And "-- bleh - never use just "id" .. this is only for example", what did you mean ? – Vincent Caggiari Jul 16 '15 at 16:10
  • In reply to your first comment - those are basic JOIN statements (as long as you understand your data set and foreign keys). I would recommend researching the structure of a JOIN statement, if you are not sure about that. For your 2nd comment - I (personally) highly dislike using JUST "id" as an identity column name .. because you get the issues as is shown in my example (where you have two tables but both have a column named "id").. A much better column name for an identity column (again, in my opinion), would be <table_name>_id. – Joishi Bodio Jul 16 '15 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.