Joining and matching tables in SQL with query

Joining and matching tables in SQL with query

So im trying to select multiple tables and do a comparison between starNumb, mvNumb from Star table and Movie table, if the two match I then want it to return / display the starName along with a total count of how many times that StarName is related to mvNumb if that makes sense.

So im trying to select multiple tables and do a comparison between starNumb, mvNumb from Star table and Movie table, if the two match I then want it to return / display the starName along with a total count of how many times that StarName is related to mvNumb if that makes sense.

So far I have this and it's not playing ball, I'm sure this is something very simple but struggling.

SELECT STARNUMB.STAR, MVNUMB.MOVIE
FROM MOVIES JOIN STAR
where STARNUMB = MVNUMB;

What I want from this is something that looks like this below as an output.

Column1 StarName = TestName1

Column2 MovNumb = 1

Meaning that star has appeared in the movie 1 x time.

The tables and FD's look something like this:

Bold = Primary keys

  • Star(starNumb, starName, brthPlce, starBorn, starDied)
  • MovStar(mvNumb*, starNumb*). <----- foreign keys
  • Movie(mvNumb, mvTitle, yrMde, mvType, crit, mpaa, noms, awrd, dirNumb*)
  • Borrow(txNumb, mvNumb*, borDte, mmbNumb*)
  • Member(mmbNumb, mmbName, mmbAddr, mmbCty, mmbSt, numRent, bonus, joinDate)
  • Director(dirNumb, dirName, dirBorn, dirDied)**


Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

An Introduction to Oracle SQL

An Introduction to Oracle SQL: A quick tour of the six principal clauses of the SELECT statement.

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

SQL Server Tutorial For Beginners - Learn MS SQL Server with Examples

SQL Server Tutorial For Beginners, Microsoft SQL Server Tutorial, SQL Server Training. Microsoft SQL Server explains all the fundamentals of MS SQL Server with examples. What is DBMS? What is SQL? What is SQL Server? SQL Server Installation