1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Sql help

Discussion in 'MySQL' started by phpsolution, Apr 25, 2012.

  1. #1
    this is database structure :http://sqlzoo.net/movie.htm

    MY problem is this "List the films where 'Harrison Ford' has appeared - but not in the star role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]"

    I wrote this query "SELECT title FROM movie inner join casting on (casting.movieid = movie.id) inner join actor (actor.id =casting.actorid) where actor.name = "Harrison Ford" and ord = 2;"

    BUT IT IS WRONG. CAN YOU TELL ME WHATS WRONG
     
    phpsolution, Apr 25, 2012 IP
  2. lektrikpuke

    lektrikpuke Active Member

    Messages:
    272
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    63
    #2
    SELECT title FROM movie WHERE casting.movieid = movie.id AND actor.id = casting.actorid AND actor.name = "Harrison Ford" AND casting.ord = 2;
     
    lektrikpuke, May 6, 2012 IP
  3. lektrikpuke

    lektrikpuke Active Member

    Messages:
    272
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    63
    #3
    If the above doesn't work, try: SELECT title FROM movie WHERE casting.movieid = movie.id AND actor.id = casting.actorid AND actor.name LIKE '%Harrison Ford%' AND casting.ord = 2;
     
    lektrikpuke, May 6, 2012 IP
  4. DennisRitchieCollective

    DennisRitchieCollective Peon

    Messages:
    44
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    This query should work for you.
    SELECT title FROM movie WHERE actor.name = 'Harrison Ford' AND casting.actorid = actor.id AND casting.ord > 1 AND movie.id = casting.movieid
     
  5. DaySeven

    DaySeven Peon

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    0
    #5
    Here's my attempt:

    Code (Text):
    1.  
    2. SELECT m.title
    3.   FROM movie m
    4.       ,actor a
    5.       ,casting c
    6.  WHERE m.id = c.movieid
    7.    AND a.id = c.actorid
    8.    AND a.name = 'Harrison Ford'
    9.    AND c.ord > 1
    10.  
     
    DaySeven, May 22, 2012 IP