mysqlunionmysql-error-1222

MySql UNION SELECT is missing fields


I'm, getting more and more frustrated because I don't understand why in my SELECT with UNION I am missing some fields. I read good documentation about this and I followed the most common example avoiding getting different number of columns between the first and the second SELECT.

It consists in adding NULL columns reaching an even-number of columns between the two statements.

Here my case:

$query = "(SELECT GC.GroupNO AS GroupNO,
    GC.Name AS Name,
    GC.Pax AS Pax,
    GC.Start AS Start,
    NULL AS custid,
    NULL AS firstname,
    NULL AS lastname,
    NULL AS arrivaldate,
    NULL AS hour,
    NULL AS pax,
    NULL AS cctype,
    NULL AS created,
    NULL AS sid,
    NULL AS ref_source,
    NULL AS numnights
  FROM LargeGroupHostels, GroupContacts AS GC
   WHERE LargeGroupHostels.HostelNumber = %d
   AND LargeGroupHostels.Status = %s
   AND LargeGroupHostels.GroupNO = GC.GroupNO";
   AND GC.Start < %s
   ORDER BY LastUpdated DESC LIMIT %d, %d
   )UNION(
    SELECT
    NULL AS GroupNO,
    NULL AS Name,
    NULL AS Pax,
    NULL AS Start,
    `custid`,
    `firstname`,
    `lastname`,
    `arrivaldate`,
    `hour`,
    `pax`,
    `cctype`,
    `created`,
    `sid`,
    `ref_source`,
    datediff(departdate, arrivaldate) AS numnights
    FROM customer_details
    WHERE hostelnumber = %d
    AND lastupdated = "" AND status != "CANX"
    AND booking_type IN ("HOSTEL", "HOSTELPACK")
    ORDER BY customer_details.arrivaldate, customer_details.created
    LIMIT %d, %d )
    ';

Apart from some optimisations and some changes that I'm aware to do, my question here is: - Why don't I get the columns GC.Name,GC.Pax,GC.Start? If I reverse the two SELECTs, it happens exactly the opposite considering only GC.Name,GC.Pax,GC.Start and avoiding the other columns.

I'm not so practice about this kind of query and it is the first time for me handling this. I'm sure I'm missing something but I don't understand what


Solution

  • You are trying to UNION for example GC.name with Name. These are interpreted as two separate columns by MySQL as it has no way to intelligently guess at your intent (and probably shouldn't have that ability). You should alias the column names in the first SELECT statement that do not currently have aliases like so:

    SELECT GC.GroupNO AS GroupNO,
    GC.Name AS Name,
    GC.Pax AS Pax,
    GC.Start AS Start,
    

    In all other cases your aliases or raw column names match.

    I would also agree with one of the comments above that this is likely better suited for a JOIN of some sort.