0

I am working on a db with only a few records, say 5 only. The DB has several columns; records being each row and a field being each column in the row/record. What's the correct Query?

db: movies
table: main

mysql> select Title, Genre, Genre2, Genre3, Actor1, Actor2, Actor3 from main;
+-------------------+-----------+-----------+---------+-----------------+-----------------------+----------------------+
| Title             | Genre     | Genre2    | Genre3  | Actor1          | Actor2                | Actor3               |
+-------------------+-----------+-----------+---------+-----------------+-----------------------+----------------------+
| Avatar            | Action    | Adventure | Fantasy | Sam Worthington | Zoe Saldana           | Sigourney Weaver     |
| Oblivion          | Action    | Adventure | Mystery | Tom Cruise      | Morgan Freeman        | Andrea Riseborough   |
| The Big Short     | Biography | Comedy    | Drama   | Christian Bale  | Steve Carell          | Ryan Gosling         |
| The Great Escape  | Adventure | Drama     | History | Steve McQueen   | James Garner          | Richard Attenborough |
| The Green Mile    | Crime     | Drama     | Fantasy | Tom Hanks       | Michael Clarke Duncan | David Morse          |
+-------------------+-----------+-----------+---------+-----------------+-----------------------+----------------------+
5 rows in set (0.00 sec)

mysql>

Problem: I have discovered (I believe) that a QUERY (eg UNION), although it combines cannot exceed the total number of records. Is this right? What I want to do is query the db and have the result be larger than the actual original table. In other words I want to ask for All Genre + All Genre2 + All Genre 3, DISTINCT; to act as a list of all possible genres in a db of 5 movies. As a movie is advertised it has up to 3 genres as shown in my examples.

Example QUERY : PHP to QUERY movies.main

$queryTable = "(SELECT Genre FROM $table_name_long)";
$queryTable .= " UNION ";
$queryTable .= "(SELECT Genre2 FROM $table_name_long)";
$queryTable .= " UNION ";
$queryTable .= "(SELECT Genre3 FROM $table_name_long);";

or simply

(SELECT Genre FROM movies.main)
UNION
(SELECT Genre2 FROM movies.main)
UNION
(SELECT Genre3 FROM movies.main);

Actual Query Result: 5 records (I suspect 5 is max result due to table size?)

Action
Biography
Adventure
Crime
Comedy

Desired Query Result: 9 records or fields combined from Genre + Genre2 + Genre3

Action
Adventure
Biography
Comedy
Crime
Drama
Fantasy
History
Mystery

Here is the table: movies.main

mysql> show create table main;
+-------+------------------------------------------------------------
---------------------------------------------------------------------
| Table | Create Table

+-------+------------------------------------------------------------
---------------------------------------------------------------------
| main  | CREATE TABLE `main` (
  `Title` varchar(100) COLLATE latin1_general_ci NOT NULL,
  `RunTime` varchar(20) COLLATE latin1_general_ci NOT NULL,
  `Genre` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Genre2` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Genre3` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Actor1` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Actor2` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Actor3` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `YearMovieMade` varchar(4) COLLATE latin1_general_ci NOT NULL,
  `Director` varchar(150) COLLATE latin1_general_ci NOT NULL,
  `Description` text COLLATE latin1_general_ci,
  `ImageLink` varchar(200) COLLATE latin1_general_ci NOT NULL,
  `Link` varchar(200) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`Title`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
+-------+------------------------------------------------------------
---------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> describe main;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| Title         | varchar(100) | NO   | PRI | NULL    |       |
| RunTime       | varchar(20)  | NO   |     | NULL    |       |
| Genre         | varchar(150) | NO   |     | NULL    |       |
| Genre2        | varchar(150) | NO   |     | NULL    |       |
| Genre3        | varchar(150) | NO   |     | NULL    |       |
| Actor1        | varchar(150) | NO   |     | NULL    |       |
| Actor2        | varchar(150) | NO   |     | NULL    |       |
| Actor3        | varchar(150) | NO   |     | NULL    |       |
| YearMovieMade | varchar(4)   | NO   |     | NULL    |       |
| Director      | varchar(150) | NO   |     | NULL    |       |
| Description   | text         | YES  |     | NULL    |       |
| ImageLink     | varchar(200) | NO   |     | NULL    |       |
| Link          | varchar(200) | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
13 rows in set (0.02 sec)

EDIT: Partial resolution - seems SQL gives desired result if done on the mysql prompt. Something strange with PHP then? Or is it me...?

mysql> (SELECT Genre FROM movies.main)
    -> UNION
    -> (SELECT Genre2 FROM movies.main)
    -> UNION
    -> (SELECT Genre3 FROM movies.main);
+-----------+
| Genre     |
+-----------+
| Action    |
| Biography |
| Adventure |
| Crime     |
| Comedy    |
| Drama     |
| Fantasy   |
| Mystery   |
| History   |
+-----------+
9 rows in set (0.00 sec)

Final thoughts about this puzzle: Since the query works in the mysql prompt as expected, but doesn't work in PHP, it gives rise to this: Is the result a temporary table then? Because I query the table and then ask for the... I just figured it out folks! Mid-sentence as I am typing now.

I was using a (counter*) to write my results in the PHP code! The "counter" was set to count the records; thus the limited results. So, it's me. But my code is awesome and perfect! Isn't it? So it reminds me why once again it isn't always a good idea to use a counter, but rather a "foreach" type of loop, if available.

Thanks to: @ypercubeᵀᴹ whom convinced me to look at the basic functionality of mysql before anything else. RESOLVED.

@ypercubeᵀᴹ go ahead and copy/paste my final query above that shows the query works and I'll accept the solution.

Improve this question
7
  • 1
    The query should have given you all 9 genres, not just 5. You must be doing something wrong on the PHP side. Try it out in the mysql command line and see.
    – ypercubeᵀᴹ
    Aug 30, 2016 at 23:17
  • @ypercubeᵀᴹ I never thought of that. I'll give it a try.
    – ejbytes
    Aug 31, 2016 at 0:09
  • @ypercubeᵀᴹ As a programmer we forget that the basics are sometimes flawed and forget to check them! Thanks. So I wonder why PHP is limiting my results? The php seems to be exactly what the MySQL is. So it's more of a PHP question then I suppose.
    – ejbytes
    Aug 31, 2016 at 0:15
  • 1
    One remark if I may. This structure will be very cumbersome to maintain in long term as you already see you had to write union queries to get all the genres. It's best to move genre to its own table and make a many-to-many relation. Then you could simply do: select * from genres and it's going to be much cheaper too.
    – Károly Nagy
    Aug 31, 2016 at 9:00
  • 1
    If you have a genres table you probably want to keep genre field unique. There's no value in having them duplicated. And you will also need a genre_movie table with genre_id and movie_id to implement the many-to-many relationship. Something like this: pastebin.com/MniYgMew This will be much faster to provide drop-down list. And also keeps your database in normal form.
    – Károly Nagy
    Sep 1, 2016 at 7:54

1 Answer 1

Reset to default
1

From here:

The default behavior for UNION is that duplicate rows are removed from the result.

So the sub-queries may well be producing the extra rows but they are then filtered before the results are seen. Try UNION ALL.

Improve this answer
3
  • 1
    It is worth noting that UNION ALL is often faster then UNION, especially for large resultsets, as UNION effectively applies DISTINCT behaviour which requires a sort which that not already be happening. Usually this sort operation results in spilling data to disk (you'll see that as "filesort" in mysql's EXPLAIN output). UNION ALL does not need to sort the data in this manner.
    – David Spillett
    Aug 31, 2016 at 13:15
  • @DavidSpillett I am currently using DISTINCT UNION, but you are saying that UNION does DISTINCT as part of it's nature, right? What is a better choice then if UNION ALL is faster, but I still need a SORT BY Genre at the tail end? (Genre...) UNION ALL (Genre2...) UNION ALL (Genre3) SORT BY Genre;
    – ejbytes
    Aug 31, 2016 at 23:45
  • Thank you for the information. It turns out that the actual problem was that I was using a COUNT to PHP my way through the UNION'ized records, where the COUNT was the record's count which limited my results via PHP code to the number of records rather than the UNION of several columns.
    – ejbytes
    Aug 31, 2016 at 23:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.