Issue using INNER JOIN on multiple tables in Postgres-CodePudding

I am trying to create a new table by using inner join to combine multiple tables. All, the tables have a primary key/column called reach_id. I have a primary table called q3_studies. I want all of the columns from this table. I then have multiple other tables that have reach_id another column. I want to JOIN this table ON reach_id that matches q3_studies but only include the other columns (so I don't have redundant reach_id columns). My first attempt seems to work if I run it from SELECT * ... using a LIMIT 1000; at the end, but adds redundant reach_ids.

SELECT * FROM second_schema.q3_studies s
INNER JOIN second_schema.bs_trigger_q3 b ON s.reach_id = b.reach_id
INNER JOIN second_schema.mod_unmod_q3 m ON s.reach_id = m.reach_id LIMIT 1000;

How can I amend this to add only the additional columns (ex: bs_trigger_q3 has an additional columns called bs_trigger, mod_unmod_q3 has an additional column called mod_unmod)?

Secondly, if I try to create a new table, I get an error: column reach_id specified more than one. What am I doing wrong here?

CREATE TABLE first_schema.report_q3 AS
SELECT * FROM second_schema.q3_studies s
INNER JOIN second_schema.bs_trigger_q3 b ON s.reach_id = b.reach_id
INNER JOIN second_schema.mod_unmod_q3 m ON s.reach_id = m.reach_id;

CodePudding user response：

Instead of select * you need to list the columns you want explicitly. This is good practice in any case. It also allows you to rename columns e.g. s.column_A as "foo_column"

In the future the schema may change.

CREATE TABLE first_schema.report_q3 AS
SELECT 
  s.reach_id, 
  s.column_A, s.column_B, 
  b.column_C, b.column_D,
  m.column_E, m.column_F
FROM second_schema.q2_studies s
INNER JOIN second_schema.bs_trigger_q3 b ON s.reach_id = b.reach_id
INNER JOIN second_schema.mod_unmod_q3 m ON s.reach_id = m.reach_id
;

If your editor does not help you with column names consider a different editor.