CODEWITHSUNDEEP
phpmysqldatabase
user1829823
user1829823

Reputation: 375

Mysql Query not receiving any data

Here's the query I'm trying to run it was working, but now for a reason I can't figure out its now given up.

SELECT films.movie_title, films.rating, films.actor, reviewed.review
      FROM films
      INNER JOIN reviewed
      ON films.movie_id=reviewed.review_id

While going through the tables I have come across this. Think this might be the problem.

ALTER TABLE `reviewed`
  ADD CONSTRAINT `reviewed_ibfk_1` FOREIGN KEY (`movie_id`) REFERENCES `films` (`movie_id`),
  ADD CONSTRAINT `reviewed_ibfk_2` FOREIGN KEY (`movie_id`) REFERENCES `films` (`movie_id`) ON DELETE CASCADE;

--
-- Table structure for table `films`
--

CREATE TABLE IF NOT EXISTS `films` (
  `movie_id` int(4) NOT NULL AUTO_INCREMENT,
  `movie_title` varchar(100) NOT NULL,
  `actor` varchar(100) NOT NULL,
  `rating` varchar(20) NOT NULL,
  PRIMARY KEY (`movie_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

--
-- Dumping data for table `films`
--

INSERT INTO `films` (`movie_id`, `movie_title`, `actor`, `rating`) VALUES
(22, 'Batman Begins', 'Bale', 'Good');

-- --------------------------------------------------------

--
-- Table structure for table `reviewed`
--

CREATE TABLE IF NOT EXISTS `reviewed` (
  `review_id` int(4) NOT NULL AUTO_INCREMENT,
  `review` mediumtext NOT NULL,
  `movie_id` int(4) NOT NULL,
  PRIMARY KEY (`review_id`),
  KEY `movie_id` (`movie_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `reviewed`
--

INSERT INTO `reviewed` (`review_id`, `review`, `movie_id`) VALUES
(1, 'Really good film.', 22);

--
ALTER TABLE `reviewed`
  ADD CONSTRAINT `reviewed_ibfk_1` FOREIGN KEY (`movie_id`) REFERENCES `films` (`movie_id`),
  ADD CONSTRAINT `reviewed_ibfk_2` FOREIGN KEY (`movie_id`) REFERENCES `films` (`movie_id`) ON DELETE CASCADE;

Upvotes: 0

Views: 81

Answers (2)

Demonslay335
Demonslay335

Reputation: 796

As @andrewsi mentions, it appears your ON clause is incorrect. You are trying to match the movie ID to the review's ID, which should in no way be assumed to be true. When an INNER JOIN fails to match correctly from the ON clause, it does typically give no results.

Try this query:

SELECT films.movie_title, films.rating, films.actor, reviewed.review
FROM films
INNER JOIN reviewed
    ON films.movie_id=reviewed.movie_id

Upvotes: 0

jdco42
jdco42

Reputation: 51

Looks like you're JOINing on two primary keys (films.movie_id=reviewed.review_id) instead of one primary and one foreign (films.movie_id=reviewed.movie_id).

Give the following a try:

SELECT films.movie_title, films.rating, films.actor, reviewed.review
      FROM films
      INNER JOIN reviewed
      ON films.movie_id=reviewed.movie_id

Upvotes: 5

Related Questions