MYSQL : How to SELECT DISTINCT from multiple databases

Question

I've got problem when i want to display my data from multiple databases. It gives duplicate values.

So I have three databases in the same server. They have same table structure. So here is my query :

Query = 'SELECT id_movie, movie_tittle
          FROM (SELECT id_movie, movie_tittle, status from margoplatinum.movie
      UNION
      SELECT id_movie, movie_tittle, status from detos21.movie
      UNION
      SELECT id_movie, movie_tittle, status from plaza21.movie) AS a
      WHERE a.status = "Now Playing"'

It works but gives the duplicate values like this :

id_movies      |        movie_tittle
------------------------------------------
      2        |          THOR 2
      1        |         Gravity
      3        |          THOR 2
      1        |  The Legend of Hercules
      4        |         Gravity

So I put the DISTINCT clauses like this :

Query = 'SELECT id_movie, DISTINCT (movie_tittle)
          FROM (SELECT id_movie, movie_tittle, status from margoplatinum.movie
          UNION
      SELECT id_movie, movie_tittle, status from detos21.movie
      UNION
      SELECT id_movie, movie_tittle, status from plaza21.movie) AS a
      WHERE a.status = "Now Playing"'

But the query above didn't work. It has an error message :

#1064 - You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'DISTINCT (movie_tittle) 
FROM (SELECT id_movie, movie_tittle, status from mar' at line 1

So how to write the SELECT DISTINCT from multiple databases?

Any help from you would be very helpful :) Thank you

score 3 · Answer 1 · answered Feb 10 '14 at 12:13

This is your query:

SELECT id_movie, movie_tittle
FROM (SELECT id_movie, movie_tittle, status from margoplatinum.movie
      UNION
      SELECT id_movie, movie_tittle, status from detos21.movie
      UNION
      SELECT id_movie, movie_tittle, status from plaza21.movie
     ) a
WHERE a.status = 'Now Playing'

It is not returning duplicates. You have id_movie in the select's, which is (apparently) different in each table.

Remove id_movie, and it will return the names of the movies with no duplicates:

SELECT movie_title
FROM (SELECT movie_title, status from margoplatinum.movie
      UNION
      SELECT movie_title, status from detos21.movie
      UNION
      SELECT movie_title, status from plaza21.movie
     ) a
WHERE a.status = 'Now Playing';

Also, storing the movies for each theater is a bad idea, from a relational database perspective. You should be storing all the movies in a single table, along with the cinema where they are playing.

aah I see..I totally forgot about the id_movie..Thank you, Sir! :) — Aprilia, Feb 10 '14 at 12:17

score 0 · Answer 2 · answered Feb 10 '14 at 12:15

Try this

SELECT DISTINCT movie_tittle, id_movie
FROM
    (SELECT 
        id_movie, movie_tittle, status
    from
        margoplatinum.movie UNION SELECT 
        id_movie, movie_tittle, status
    from
        detos21.movie UNION SELECT 
        id_movie, movie_tittle, status
    from
        plaza21.movie) AS a
WHERE
    a.status = 'Now Playing';

MYSQL : How to SELECT DISTINCT from multiple databases

2 Answers2