CODEWITHSUNDEEP
sql
Darius T
Darius T

Reputation: 1

How can I take a joined table and use it to reference another table?

I've just started learning SQL and need help with an assignment question. I am asked to look through a dataset about Kickstarter campaigns. I'm asked to find the top 3 categories by amount of backers.

Here is the ER diagram:

ER diagram

In the 'Campaign' Table, there's the 'backers' column, but the 'Category' Table is only related with the Campaign through the 'Sub-Category' Table.

So far, I have been able to Join sub_category.category_id with the sub-category.category_name, but i'm not sure how to take this new Table and join it with Campaign

SELECT C.name AS category_name, SC.category_id, SC.id AS SC_id
FROM Category AS C
JOIN sub_category AS SC ON C.id = SC.category_id

Screenshot

I am hoping to have a table where there is a column for 'Category Name' and 'Backers' and then simply sort it by the number of backers

How should I go about this? Am I on the right track?

Upvotes: 0

Views: 110

Answers (2)

DRapp
DRapp

Reputation: 48129

And just to take Magnus's answer and rewrite visually, you can better see the hierarchy of the query. See how it closely resembles that of your table relationships

SELECT 
      C.name category_name, 
      CA.backers
   FROM 
      campaign CA
         JOIN sub_category SC
            ON CA.sub_category_id = SC.Id
            JOIN Category C 
               ON SC.category_id = C.id
   order by 
      CA.backers

Notice the indentation to the table its ID is based upon from that prior to it. This way you know which column FROM connecting TO. I have found that if you list the tables in the FROM clause first to show all the HOW tables are related and ON what foreign : primary key relationships, that is the hardest part. Then its just pulling the columns you want after that.

Upvotes: 0

ishant kaushik
ishant kaushik

Reputation: 951

SELECT C.name AS category_name, CA.backers
FROM campaign AS CA
JOIN sub_category AS SC
ON CA.sub_category_id =SC.Id
JOIN Category AS C 
ON C.id = SC.category_id
order by CA.backers

You can have multiple joins all together in one query.

Secondly there is a connection between Campaign and Sub_Category table which will help to join these two tables.

Later we can then join Category table as these two table has a connection between them based on Category_Id which is a foreign key in sub category table.

At last you can just order by based on Backers. Let me know if you have any issue or doubt in comments.

Upvotes: 2

Related Questions