CODEWITHSUNDEEP
google-bigquery
Maykid
Maykid

Reputation: 517

3 Table Join SQL Big Query

I'm new to SQL joins so bear with me, what I'm trying to do is list out student test scores by week added up per student. The query I have is,

SELECT
  agg.Week,
  meta.Category,
  agg.Student,
  meta.Funding,
  agg.Test_Results,
  agg.Test_Points,
  phy.Test_Results,
  phy.Test_Points
FROM Agriculture agg

INNER JOIN metadata meta
 ON meta.Student_ID = agg.Student_ID

INNER JOIN Physics phy
 ON phy.Student_ID = agg.Student_ID

WHERE agg.Week BETWEEN '1' AND '5'

To me this looks correct, but for some reason what is happening is the Test_Results and Test_Points are duplicating themselves over and over and I'm not sure how to fix it. What I mean by duplicating is, since the points are the same 120 each week, a student(9) will score a 95, that 95 will repeat itself for that same student over and over again. I'm not sure if I need to have a subquery added to the INNER JOINS or not.

Tables are:

metadata --> (Category, Funding, Student_ID) (One to many comparison table)

Physics --> (Test_Results, Test_Points, Student_ID) (Many to many)

Agriculture --> (Week, Student, Test_Results, Test_Points, Student_ID) (Many to Many)

Again, I'm new to joins so if you need more information please let me know.

Upvotes: 0

Views: 43

Answers (2)

Ranvir
Ranvir

Reputation: 191

You can simply use DISTINCT keyword for agg.Student.

Upvotes: 0

iLikeMySql
iLikeMySql

Reputation: 746

You join the table by using Student_ID. If there is more than one corresponding row in metadata for the same Student_ID from Agriculture the resultset will show duplicates. The same goes for table Physics.

for example : You have 10 rows in table Agriculture for a Student_ID and 2 rows in metadata with the same Student_ID, you will get 20 rows if you join the tables.

You can indeed use subqueries to reduce the number of rows per Studen_ID in metadata and Physics and join on those subselects.

Upvotes: 1

Related Questions