MYSQL querying two tables with different fields

Question

Here is my search query, which is putting out an argument error.
What do I need to do to correct it. Basically I need to put both of these tables together and pull out the next 5 events within the two tables by the start date. You will also notice that the last field in each table is different from one another.

$CEsql = mysql_query("
  SELECT id, title, start, end, allDay
  FROM calendar 
  WHERE mem_id='$logOptions_id' 
    AND start >= DATE_SUB( CURDATE( ) ,INTERVAL 0 MONTH ) 
  
  UNION ALL 

  SELECT id, title, start, end, allDay
  FROM team_calendar 
  WHERE team_id IN ($team_array) 
    AND start >= DATE_SUB( CURDATE( ) ,INTERVAL 0 MONTH ) 
  ORDER BY start ASC LIMIT 5");

I am using MYSQL 5.0 and PHP.

Update

mysql_error displays:

1267: Illegal mix of collations for operation 'UNION'

@Dagon mysql_fetch_array() supplied argument is not a valid mysql result resource — Juan Gonzales, Feb 27 '12 at 03:58
@Juan Gonzales: use `mysql_error()` and next time check your every single query in mysql console or PMA (before you ask here) to check if the query is correct — zerkms, Feb 27 '12 at 03:59
I think people think php and mysql are the same thing so that errors from mysql should show as errors from php do; that or they just dumb! — , Feb 27 '12 at 04:08
@MikeB mysql_error displays - Illegal mix of collations for operation 'UNION' — Juan Gonzales, Feb 27 '12 at 04:15
both tables\fields need the same collation for union to work — , Feb 27 '12 at 04:18
@JuanGonzales, please show the outputs for `SHOW CREATE TABLE calendar;` and `SHOW CREATE TABLE team_calendar;` — Abhay, Feb 27 '12 at 13:58

score 1 · Answer 1 · answered Feb 27 '12 at 03:57

1

Please check in WHERE clause. Use mem_id not id2. id2 is just alias not a field name.

$CEsql = mysql_query("
  SELECT id, title, start, end, allDay, mem_id as id2 
  FROM calendar 
  WHERE mem_id='$logOptions_id' 
    AND start >= DATE_SUB( CURDATE( ) ,INTERVAL 0 MONTH ) 

  UNION ALL 

  SELECT id, title, start, end, allDay, team_id as id2 
  FROM team_calendar 
  WHERE team_id IN ($team_array) 
    AND start >= DATE_SUB( CURDATE( ) ,INTERVAL 0 MONTH ) 
  ORDER BY start ASC LIMIT 5");

answered Feb 27 '12 at 03:57

Leap Bun

2,265
5
28
44

@JuanGonzales Run this query in any sql editor, and show the error message. – Leap Bun Feb 27 '12 at 04:04
Error is - Illegal mix of collations for operation 'UNION' – Juan Gonzales Feb 27 '12 at 04:19
@JuanGonzales Check collation of fields which are varchar (any related text type). In here it should be in title field. Make sure the collation of title field in calendar and team_calendar table are the same. – Leap Bun Feb 27 '12 at 04:27
clearly not or you wouldn't get the error, its *Collation* not type and its both fields and table. – Feb 27 '12 at 04:38
Drop "title" from query string and try the query. Does it show any errors – Naveen Kumar Feb 27 '12 at 07:39

score 0 · Accepted Answer · answered Feb 27 '12 at 07:52

Run SHOW CREATE TABLE statements for both tables. Find differences between two tables, check charsets and collations as Leap Bun suggested. Then you may change field charset/collation, or just try to use COLLATE clause in your SELECT query. Something like this -

SELECT id, title, start, end, allDay
  FROM calendar 
  WHERE mem_id='$logOptions_id' 
    AND start >= DATE_SUB( CURDATE( ) ,INTERVAL 0 MONTH ) 

  UNION ALL 

  SELECT id, 
    title COLLATE collation_name AS title, -- write your collation here
    start, end, allDay
  FROM team_calendar 
  WHERE team_id IN (1,2) 
    AND start >= DATE_SUB( CURDATE( ) ,INTERVAL 0 MONTH ) 
  ORDER BY start ASC LIMIT 5

MYSQL querying two tables with different fields

Update

2 Answers2