0

I'm having some problems with INTERSECT command. Hope someone could help me.

I want to get the the movieid that appears in the first and second SELECT. After that I want to use these data (that could be in a LIMIT of 10) to receive the titles of the movie in another table.

Something like this, but I'm not doing right:

SELECT movieid
FROM ratings
WHERE votes > 0
  INTERSECT SELECT movieid FROM genres WHERE genre = '$_SESSION[genero]'

In this case I should get the movied that appear both on ratings and genres tables.

After this, I want to get these movieids and search the table movies for the movieid and finally show the title. Thank you!

Nguyễn Hải Triều
  • 1,454
  • 1
  • 8
  • 14
Léo Eduardo Silva
  • 131
  • 11

2 Answers2

0

As far as I understood the question, I think this is what you are looking for

select title , movieid from movies
where movieid in 
(
        SELECT movieid FROM ratings
        WHERE votes > 0
    INTERSECT 
        SELECT movieid FROM genres 
        WHERE genre = '$_SESSION[genero]'
)
Utsav
  • 7,914
  • 2
  • 17
  • 38
  • Works like a charm, in parts! The only problem is that the '$_SESSION[genero]' can't be recognized. If I change this, it works. Any idea of what could I do to make '$_SESSION[genero]' works? edit: I'm using the $_SESSION[genero] and it shows with no problem the value, but when I put this in the QUERY, it doesn't work... – Léo Eduardo Silva Oct 23 '15 at 05:01
  • Sorry I dont have idea about postgresql. I assume that `SELECT movieid FROM genres WHERE genre = '$_SESSION[genero]` is working for you, so the rest of the query is on top of that. – Utsav Oct 23 '15 at 05:04
0
select distinct m.title , movieid
from
    movies m
    inner  join
    ratings r using (movieid)
    inner join
    genres g using (movieid)
where r.votes > 0 and g.genre = '$_SESSION[genero]'
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260