koc koc - 3 months ago 9
MySQL Question

Select from two or more sql tables where have some column names

I have 6 table where all columns name is same. Now I want to select only those table where new = '1'.

For example:

Table 1: 3d_movie
+----+------+-----+------+
| id | name | new | date |
+----+------+-----+------+
| 1 |name1 | 0 | 10.00|
| 2 |name1 | 0 | 10.00|
| 3 |name2 | 1 | 10.00|
+----+------+-----+------+

Table 2: english_movie
+----+------+-----+------+
| id | name | new | date |
+----+------+-----+------+
| 1 |name1 | 0 | 10.00|
| 2 |name1 | 1 | 10.00|
| 3 |name2 | 0 | 10.00|
+----+------+-----+------+

Table 3: hindi_movie
+----+------+-----+------+
| id | name | new | date |
+----+------+-----+------+
| 1 |name1 | 1 | 10.00|
| 2 |name1 | 0 | 10.00|
| 3 |name2 | 0 | 10.00|
+----+------+-----+------+


When admin add a new movie on his server, he select new = 1.
So if client go to new movies page, I want to search all movie from all table where new = '1'.

I tried by these way but it's not work, please help me..

Tried 1: $result = mysqli_query($db,"SELECT * FROM 3d_movie,english_movie,hindi_movie WHERE 3d_movie.new='1' and english_movie.new='1' and hindi_movie.new='1 ORDER BY date DESC LIMIT 30");

Tried 2: $result = mysqli_query($db,"SELECT * FROM 3d_movie,english_movie,hindi_movie WHERE new='1' ORDER BY date DESC LIMIT 30");

Answer

You have to frame a SQL query using UNION. Try the following query:

SELECT *
FROM 3D_MOVIE
WHERE NEW = 1

UNION

SELECT *
FROM ENGLISH_MOVIE
WHERE NEW = 1

UNION

SELECT *
FROM HINDI_MOVIE
WHERE NEW = 1
Comments