Applying multiple conditions SQL?

Question

I have an assignment that I am having trouble with, since this is for my studies I would appreciate it if you didn't give me the answer straight up, but instead gave me clues (I don't want to cheat).

I have three tables: Movies, Stars and ActsIn. I want to retrieve movies By a certain actor (in this case, Cameron Diaz) but I only want to display her LATEST movie. So basically there are two conditions, A name condition, and a year released condition.

I am very close to completing it, but I am stuck on a vital part. If I apply the conditions separately they work, but I need to have it all in the one statement. This is what i have so far.

SELECT M.Title, M.Yearreleased
FROM Movies M NATURAL JOIN Stars S NATURAL JOIN ActsIn A
WHERE Yearreleased = (SELECT MAX(Yearreleased) FROM Movies)

This will display the movies released in the latest year. If i swap the WHERE condition of this statement with:

WHERE S.Familyname = 'Diaz'

I will get all the movies Cameron Diaz acted in.

I have tried the following code combinations that I thought would work:

SELECT M.Title, M.Yearreleased
FROM Movies M NATURAL JOIN Stars S NATURAL JOIN ActsIn A
WHERE Yearreleased = (SELECT MAX(Yearreleased) FROM Movies) AND S.Familyname = 'Diaz'

My results from this query was literally an empty table.

All i guess i need, is for you guys to give me clues as to how i can join these two conditions into the one statement. THANK YOU! Let me know if you need sample data or a Fiddle

UPDATE

I think I may have solved it, although I did it by complete accident, maybe you could explain to me why it works? The query is:

SELECT M.Title, M.Yearreleased
FROM   Movies M 
       NATURAL JOIN Stars S 
       NATURAL JOIN ActsIn A 
WHERE  Yearreleased = (SELECT MAX(Yearreleased) 
                       FROM   Movies M 
                              NATURAL JOIN Stars S 
                              NATURAL JOIN ActsIn A 
                       WHERE  S.Familyname = 'Diaz')

AND S.Familyname = 'Diaz'

This outputs the latest movie released by Cameron Diaz.

You need to use a group by query where it selects the Max of a date for the family name. This does not need to be a nested query. — Valamas, Aug 21 '13 at 03:53
what is your error message, although there are many way to make it, but in order to learn from your error, u need to share out what is your error message, and lets others explain for u =) — Low Chee Mun, Aug 21 '13 at 04:05
You can put AND clause between conditions like: WHERE column1='x' AND column2>100 — Ran Avnon, Aug 21 '13 at 04:27
actually ur problem is JOIN, the best way to perform join is to add in some condition like movie.movieid = actor.movieid, when u add in correct join, i believe there are no problem in perform ur where statement, cheer — Low Chee Mun, Aug 21 '13 at 05:38
The subquery that selects max(yearreleased) should also make sure that the family name is 'Diaz' (Another join would be needed for this in tha subquery). Otherwise it could be tha maximum release year of all actors. — phoenixgr, Aug 21 '13 at 05:54

score 1 · Accepted Answer · answered Aug 21 '13 at 05:55

so i will explain in here, take the example below as sample, the first time ur nature join is like below

Sample data

Table A
A.Yearreleased | A.col2
A.data1        | A.data2

Table B
B.col 1     | B.Familyname 
B.data1     | B.data2

after join

Table AB
A.Yearreleased | A.col2 | B.col 1     | B.Familyname 
A.data1        | A.data2| NULL        | NULL
NULL           | NULL   | B.data1     | B.data2

so in here u are trying to search for A.data1 AND B.data2 in your where condition, but in your table, there are no such records, so there are no result return

the 2nd time u perform a join with condition (still no proper enough, it still can be improve >< ), your table is like below

Table AB
A.Yearreleased | A.col2 | B.col 1     | B.Familyname 
A.data1        | A.data2| B.data1     | B.data2

with 2nd improved query, u able to get ur data =)

hope my explanation able to help u to understand what is your mistake, cheer

Ohh okay I get you. So should I try and improve my query, or do you think it works fine. I am not sure if the teachers will mark me down for inefficient code. Thanks for the quick reply — user1330649, Aug 21 '13 at 06:03
steady, if this is school project, i believe your lecturer will not check on your code as long as there are no performance issues, haha, but for your future, this is good that u can improve your query =) p.s: please mark me if you feel i do helped you =) — Low Chee Mun, Aug 21 '13 at 06:06

Applying multiple conditions SQL?

1 Answers1