SQL Join is bringing in too many columns

Question

I have two data sets: attendance at a day care in September & attendance at a day care in October. The columns in the data sets are student ID, first name, last name, and then each date of the month. The value is marked as a 1 if they attended that day and a 0 if they didn't. Looking to pull this together into one data set so it shows a smooth transition from September to October.

SELECT
  *
FROM
  `ready-set-grow-analysis.RSG_Analysis.Sep_Att` AS Sep
FUll JOIN
  `ready-set-grow-analysis.RSG_Analysis.Oct_Att` AS Oct
ON
  Oct.First_Name = Sep.First_Name

But after the 09/30/2022 column rather than going straight into 10/01/2022, it creates three more columns that say Student_Id_1, First_Name_1, and Last_Name_1 before proceeding.

You're joining two tables so you get the columns from both tables in the results. `FULL JOIN` is almost certainly the wrong operation because it matches every row in one table with every row in the other. If the tables had 100 rows each, you end up with 10000 rows — Panagiotis Kanavos, Mar 14 '23 at 15:45
If you want to use only some of the columns, why did you use `SELECT *`? Specify which columns you need instead of `*` — Nico Haase, Mar 14 '23 at 15:46
Just a suggestion - why not have 1 table - witha month column - and do something like: SELECT * FROM RSG_Analysis WHERE Month in ('Sept', 'Oct') AND First_Name = 'Fred' — David McEleney, Mar 14 '23 at 15:52
Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Mar 14 '23 at 22:19

Cetin Basoz · Answer 1 · 2023-03-14T15:52:55.273

They have the same structure, right?

SELECT 'September' as MonthName,*
FROM
  `ready-set-grow-analysis.RSG_Analysis.Sep_Att` AS Sep
union all
SELECT 'October' as MonthName, *
FROM  `ready-set-grow-analysis.RSG_Analysis.Oct_Att` AS Oct

BTW, if their structure is different and you literally used dates as column names (oh well a big mistake start over really):

SELECT < list resulting columns you want here explicitly >
FROM
  `ready-set-grow-analysis.RSG_Analysis.Sep_Att` AS Sep
FUll JOIN
  `ready-set-grow-analysis.RSG_Analysis.Oct_Att` AS Oct
ON
  Oct.First_Name = Sep.First_Name

(You should see the pain in the query itself - your columns should simply look like Day01...Day31 and would accommodate to any month. Better yet just one column as theDate which you can later pivot -crosstab- as you see fit)

SQL Join is bringing in too many columns

1 Answers1