CODEWITHSUNDEEP
sqlsql-serversql-server-2008
damowon
damowon

Reputation: 5

Compare and retrieve numbers in SQL

 Actsin table            Stars table                 Movies table

Starid  Movienum      Starid  Gname Fname      Movienum  Title  Yearreleased
  100     1000          100    Matt Damon        1000     God     2002

Actsin, Stars, Movies are three different tables.

How can I get "Matt Damon"'s most recent movie with the movie name and year released?

I got stuck at how to compare and get the latest year.

Upvotes: 0

Views: 93

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269447

This is a basic join query, with an order by and top clause (which might be limit or something else depending on the database):

select top 1 m.title, m.yearreleased
from movies m join
     actsin ai
     on ai.movienum = m.movienum join
     stars s
     on ai.starid = s.starid
where gname = 'Matt' and fname = 'Damon'
order by m.yearreleased desc;

What do gname and fname stand for? In particular, what "fname" would be "Damon" for "Matt Damon"?

EDIT:

You are probably using a different database. This might work:

select m.title, m.yearreleased
from movies m join
     actsin ai
     on ai.movienum = m.movienum join
     stars s
     on ai.starid = s.starid
where gname = 'Matt' and fname = 'Damon'
order by m.yearreleased desc
limit 1;

If not, you need to figure out what database you are using. That is important if you are writing SQL for it.

Upvotes: 2

Related Questions