mysql query - how to merge tables in another table

Question

I have a problem joining tables in the result column. i have a working query which combine different tables using UNION but when i'm extending another table i got an error saying 'The used SELECT statements have a different number of columns'

this is my query:

(SELECT
    IDNumber,
    CONCAT(LastName, ', ', FirstName, ' ', Middle) as Name,
    CONCAT(EmDesignation, ', ', Department) as Information,
    Image,
    v.PlateNo as PlateNumber
FROM
    tblemployee as e, tblvehicle as v
WHERE 
    v.RFIDNo LIKE '6424823943'
AND
    e.RFIDNo LIKE '6424823943')
UNION
(SELECT 
    IDNumber,
    CONCAT(LastName, ', ', FirstName, ' ', Middle) as Name,
    CONCAT(Course, ', ', Year) as Information,
    Image,
    v.PlateNo as PlateNumber
FROM
    tblstudents as s, tblvehicle as v
WHERE
    v.RFIDNo LIKE '6424823943'
AND
    s.RFIDNo LIKE '6424823943')

I have problem with this. Continuation query above

UNION
(SELECT
    Barrier
FROM 
    tblinformation as inf
WHERE
    inf.RFIDNo IN (6424823943)
ORDER BY 
    AttendanceNo DESC LIMIT 1)

When you use UNION the quantity of columns, the order and types, should be the same for all the queries you are making the union. — Shidersz, Oct 26 '18 at 16:20
I think i'm wrong doing UNION... can you give me solution to join that table? — Kervy Hipona, Oct 26 '18 at 16:36
Please, add sample data and the expected output, so we don't have to guess what you need. If you do this, i will help you with your query. — Shidersz, Oct 26 '18 at 18:46
Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Oct 28 '18 at 11:56

score 0 · Answer 1 · edited Oct 26 '18 at 20:53

0

The error message states what the problem is. Just improve number of columns in SELECT and it will work correctly.

edited Oct 26 '18 at 20:53

Brad Larson

170,088
45
397
571

answered Oct 26 '18 at 16:18

step

2,254
2
23
45

gaborsch · Answer 2 · 2018-10-26T16:33:45.810

0

The error message is correct. Add NULLs to your second query to match the column number, and it will work.

For example:

SELECT
    Barrier,
    NULL,
    NULL,
    NULL,
    NULL
FROM 
    tblinformation as inf
...

edited Oct 26 '18 at 16:33

answered Oct 26 '18 at 16:19

gaborsch

15,408
6
37
48

I think i'm wrong doing UNION... can you give me solution to join that table? – Kervy Hipona Oct 26 '18 at 16:45
it inserted to another row ... I think UNION is not the solution.. can u please help joining it? – Kervy Hipona Oct 26 '18 at 16:50

mysql query - how to merge tables in another table

2 Answers2