CODEWITHSUNDEEP
mysqlsql
user5174952
user5174952

Reputation: 147

Combine Two Select Statements Using UNION

I am learning some SQL statements, I cannot see why my code cannot execute from my database:

(SELECT Time FROM mytable)
UNION
(SELECT Travel FROM mytable);

The above statement is forbidden.

Upvotes: 0

Views: 113

Answers (2)

Hogan
Hogan

Reputation: 70538

If they are different types I'm guessing you want this:

SELECT Time as TimeValue, null as TravelValue FROM mytable
UNION ALL
SELECT null as TimeValue, Travel as TravelValue FROM mytable;

or this

SELECT Time, Travel FROM mytable;

Upvotes: 0

SQL Tactics
SQL Tactics

Reputation: 296

It does not look like columns called Time and Travel would be of the same datatype... When you UNION two select statements, you are effectively putting the two values in the same column. SQL does not know what type to make the column. Likely, it sees the Time values, makes the column a DATETIME column, an then you start trying to cram Travel VARCHAR strings into it.

Try casting both as the same type, like this:

SELECT
  DATE_FORMAT(Time, '%Y-%m-%d %h:%i:%s') Time
FROM myTable

UNION

SELECT
  Travel
FROM myTable;

Upvotes: 1

Related Questions