UNION 2 Table with different Column name and different number of columns-CodePudding

I have MySQL Query

$result = $db->rows("SELECT calendar_id, calendar_title, calendar_starttime, calendar_endtime
FROM (SELECT academic_schedule_id AS calendar_id, academic_schedule_name AS calendar_title, academic_schedule_datetimestart AS calendar_starttime, academic_schedule_datetimeend AS calendar_endtime, academic_schedule_status, academic_schedule_faculty
      FROM academic_schedule_table
            WHERE academic_schedule_faculty = 'Teknik Mesin' AND academic_schedule_status = 'Active'
      UNION ALL
      
      SELECT appointment_table.dosen_schedule_id AS calendar_id, appointment_table.dosen_id, appointment_table.status, dosen_schedule_table.dosen_id, dosen_schedule_table.dosen_schedule_name AS calendar_title, dosen_schedule_table.dosen_schedule_datetimestart AS calendar_starttime, dosen_schedule_table.dosen_schedule_datetimeend AS calendar_endtime 
      FROM appointment_table  
            INNER JOIN dosen_table 
            ON dosen_table.dosen_id = appointment_table.dosen_id 
            INNER JOIN dosen_schedule_table 
            ON dosen_schedule_table.dosen_schedule_id = appointment_table.dosen_schedule_id 
            WHERE appointment_table.siswa_id = ".$_SESSION['siswa_id']." AND appointment_table.status = 'Disetujui'
     ) ab
    GROUP BY calendar_id, calendar_title, calendar_starttime, calendar_endtime");

From that query i need 4 column (calendar_id, calendar_title, calendar_starttime, calendar_endtime)

As u can see the first SELECT query SELECT academic_schedule_id AS calendar_id ... is having different number of column from the second SELECT query SELECT appointment_table.dosen_schedule_id AS calendar_id ...

But it give me Cardinality violation: 1222 The used SELECT statements have a different number of columns which one i do it wrong?

What's wrong with it?

CodePudding user response：

Change your statement, so that you have the same number (and type) in both select statements:

$result = $db->rows("SELECT calendar_id, calendar_title, calendar_starttime, calendar_endtime
FROM (SELECT academic_schedule_id AS calendar_id, academic_schedule_name AS calendar_title, academic_schedule_datetimestart AS calendar_starttime, academic_schedule_datetimeend AS calendar_endtime
      FROM academic_schedule_table
            WHERE academic_schedule_faculty = 'Teknik Mesin' AND academic_schedule_status = 'Active'
      UNION ALL
      
      SELECT appointment_table.dosen_schedule_id AS calendar_id, dosen_schedule_table.dosen_schedule_name AS calendar_title, dosen_schedule_table.dosen_schedule_datetimestart AS calendar_starttime, dosen_schedule_table.dosen_schedule_datetimeend AS calendar_endtime 
      FROM appointment_table  
            INNER JOIN dosen_table 
            ON dosen_table.dosen_id = appointment_table.dosen_id 
            INNER JOIN dosen_schedule_table 
            ON dosen_schedule_table.dosen_schedule_id = appointment_table.dosen_schedule_id 
            WHERE appointment_table.siswa_id = ".$_SESSION['siswa_id']." AND appointment_table.status = 'Disetujui'
     ) ab
    GROUP BY calendar_id, calendar_title, calendar_starttime, calendar_endtime");

CodePudding user response：

There are more columns in the second select statement than the first one.

There are 6 columns in first select statement and 7 columns in the second statement.

You need to change the column ORDINAL POSITION to match the data or cast/convert the data type to match both.