Want to concatenate column of the second query to the first query but getting errors such as “query block has incorrect number of result columns”

SELECT
    ID, PRIM_EMAIL, SEC_EMAIL, PHONE 
FROM
    STUDENTS.RECORDS
WHERE 
    ID IN (SELECT ID FROM STUDENTS.INFO WHERE ROLL_NO = '554')

UNION

SELECT NAME 
FROM STUDENTS.INFO 
WHERE ROLL_NO = '554';

Here Roll_No is a user inserted data so for now I have hard coded it. Basically with the help of ROLL_NO I sort the STUDENTS_INFO table from where I get the ID and based on that I try to get PRIM_EMAIL, SEC_EMAIL, PHONE from the STUDENTS.RECORDS table while matching the foreign keys of both the tables. In addition to the current result set I also want to have the prov_name column.

Any help is very much appreciated. Thank you!

Answer

I suspect that you want to put all this information on the same row, which suggests a join rather than union all:

select 
    r.ID,
    r.PRIM_EMAIL, 
    r.SEC_EMAIL,
    r.PHONE,
    r.NAME
from STUDENTS.RECORDS r
inner join STUDENTS.INFO i ON i.ID = r.ID
where I.ROLL_NO = '554';