MySQL Forums
Forum List  »  Connector/Node.js

how can I connect multiple select queries via UNION ALL.
Posted by: Kiumars Jahandel
Date: June 28, 2020 11:42PM

I have more than 61 joins in SELECT-query, so I split into 3 SELECT and tried to connect to UNION ALL.
I want to connect 2 SELECT queries to UNION ALL, but it doesn't work.
are there any alternatives?

exports.getAllMessagesForListId = function(req, res) {

let list_id = req.body.list_id;

let sqlData = `
SELECT AS id, m.user_id AS user_id, m.receiver_id AS receiver_id, m.requested_for_id AS requested_for_id, m.assigned_id AS assigned_id,
m.opened_by_id AS opened_by_id,m.room_number AS room_number, m.contact_number AS contact_number, m.inventory_number AS inventory_number, m.datetime AS datetime,
(SELECT COUNT(id) FROM message WHERE list_id = m.list_id) AS messageCount, m.last_message_count AS last_message_count,
m.subject AS subject,m.message AS message, m.work_message AS work_message, m.reason_message AS reason_message, m.open_again_reason_message AS open_again_reason_message, m.todo AS todo,
m.is_request AS is_request, m.is_new AS is_new, m.done AS done, m.importance_id AS importance_id,
m.sla_message_response_in_hour AS sla_message_response_in_hour, m.sla_job_done_in_day AS sla_job_done_in_day,
FLOOR(HOUR(TIMEDIFF(m.created, CURRENT_TIMESTAMP())) / 24) AS day, m.created As messageCreated, m.updated AS messageUpdated,
ud.external_company AS external_company, AS country, AS city, ud.post_code AS post_code, ud.street_and_house_number AS street_and_house_number,
ud.floor AS floor, AS room, ud.table_number AS table_number, ud.department AS department, ud.phone_number AS phone_number,
ud.company_card_number AS company_card_number, ud.cost_centre AS cost_centre, ud.position AS position, ud.about_user AS about_user,
ud.created AS user_details_created, ud.updated AS user_details_updated,
(SELECT image FROM images WHERE user_id = m.user_id) AS imageItem,
mreq.number AS request_number,
minc.number AS incident_number,
mpro.number AS problem_number,
mtas.number AS task_number, AS group_id, AS group_name, AS assigned_group_name, AS location_id, AS location_name, AS location_id, AS location_name, AS service_category_id, AS service_category_name, AS category_id, AS category_name, AS application_id, AS application_name, AS database_server_id, AS database_server_name, AS end_user_asset_id, AS end_user_asset_name, AS network_id, AS network_name, AS peripheral_system_id, AS peripheral_system_name, AS server_id, AS server_name, AS is_resolved_id, AS is_resolved_name, isr.icon AS is_resolved_icon, isr.color AS is_resolved_color, AS impact_id, AS impact_name, AS contact_type_id, AS contact_type_name, AS priority_id, AS priority_name, AS priority_problem_id, AS priority_problem_name, AS urgency_id, urgency_name, AS source_id, AS source_name, AS image_id, AS name, im.image AS image, im.size AS size, im.created AS image_created, AS company, u.firstname AS firstname, u.lastname AS lastname, AS email, u.role AS role, u.color AS color,
u.created AS user_created, u.updated AS user_updated, AS requested_for_email, CONCAT(ur.firstname, " ", ur.lastname) AS requested_for, AS requested_for_company, AS opened_by_email, CONCAT(uo.firstname, " ", uo.lastname) AS opened_by, AS opened_by_company, AS receiver_email, CONCAT(ure.firstname, " ", ure.lastname) AS receiver_member, AS receiver_company, AS assigned_email, CONCAT(uas.firstname, " ", uas.lastname) AS assigned_member, AS assigned_company
FROM message AS m
ON = m.user_id
LEFT JOIN user AS ure
ON = m.receiver_id
LEFT JOIN user_details AS ud
ON ud.user_id = m.user_id
LEFT JOIN images AS im
ON = m.image_id
LEFT JOIN user AS ur
ON = m.requested_for_id
LEFT JOIN user AS uo
ON = m.opened_by_id
LEFT JOIN user AS uas
ON = m.assigned_id
LEFT JOIN user_locations AS usl
ON usl.user_id = m.user_id
LEFT JOIN message_requests AS mreq
ON mreq.list_id = m.list_id
LEFT JOIN message_incidents AS minc
ON minc.list_id = m.list_id
LEFT JOIN message_problems AS mpro
ON mpro.list_id = m.list_id
LEFT JOIN message_tasks AS mtas
ON mtas.list_id = m.list_id
LEFT JOIN locations AS l
ON = usl.location_id
LEFT JOIN message_locations AS msl
ON msl.list_id = m.list_id
LEFT JOIN locations AS lo
ON = msl.location_id
LEFT JOIN user_groups AS usg
ON usg.user_id =
LEFT JOIN groups AS gr
ON = usg.group_id
LEFT JOIN user_groups AS usga
ON usga.user_id = m.assigned_id
LEFT JOIN groups AS gra
ON = usga.group_id
LEFT JOIN message_service_category AS mser
ON mser.list_id = m.list_id
LEFT JOIN service_category AS ser
ON = mser.service_category_id
LEFT JOIN message_category AS mca
ON mca.list_id = m.list_id
LEFT JOIN category AS cat
ON = mca.category_id
LEFT JOIN category_application AS capl
ON capl.list_id = mca.list_id
LEFT JOIN category_database_server AS cdas
ON cdas.list_id = mca.list_id
LEFT JOIN category_end_user_asset AS cnua
ON cnua.list_id = mca.list_id
LEFT JOIN category_network AS cane
ON cane.list_id = mca.list_id
LEFT JOIN category_peripheral_system AS cpri
ON cpri.list_id = mca.list_id
LEFT JOIN category_server AS cerv
ON cerv.list_id = mca.list_id
LEFT JOIN application AS apl
ON = capl.application_id
LEFT JOIN database_server AS das
ON = cdas.database_server_id
LEFT JOIN end_user_asset AS enua
ON = cnua.end_user_asset_id
LEFT JOIN network AS netw
ON = cane.network_id
LEFT JOIN peripheral_system AS peri
ON = cpri.peripheral_system_id
LEFT JOIN server AS serv
ON = cerv.server_id
LEFT JOIN message_impact AS mimp
ON mimp.list_id = m.list_id
LEFT JOIN impact AS imp
ON = mimp.impact_id
LEFT JOIN message_contact_types AS mctp
ON mctp.list_id = m.list_id
LEFT JOIN contact_types AS ctps
ON = mctp.contact_type_id
LEFT JOIN message_priority AS mspr
ON mspr.list_id = m.list_id
LEFT JOIN priority AS pri
ON = mspr.priority_id
LEFT JOIN message_priority_problem AS mprp
ON mprp.list_id = m.list_id
LEFT JOIN priority_problem AS prop
ON = mprp.priority_problem_id
LEFT JOIN message_is_resolved AS misr
ON misr.list_id = m.list_id
LEFT JOIN is_resolved AS isr
ON = misr.is_resolved_id
LEFT JOIN message_urgency AS meur
ON meur.list_id = m.list_id
LEFT JOIN urgency AS urg
ON = meur.urgency_id
LEFT JOIN message_sources AS meso
ON meso.list_id = m.list_id
LEFT JOIN sources AS sor
ON = meso.source_id
(SELECT AS id, AS message_group_id, AS message_group_name, AS service_id, AS service_name, AS set_state_id, AS set_state_name, sets.color AS set_state_color, sets.icon AS set_state_icon, AS set_sub_state_id, AS set_sub_state_name, AS waiting_id, AS waiting_name
FROM message AS m
LEFT JOIN message_groups AS msg
ON msg.list_id = m.list_id
LEFT JOIN groups AS gro
ON = msg.group_id
LEFT JOIN message_services AS mese
ON mese.list_id = m.list_id
LEFT JOIN services AS sers
ON = mese.service_id
LEFT JOIN message_set_state AS mess
ON mess.list_id = m.list_id
LEFT JOIN set_state AS sets
ON = mess.set_state_id
LEFT JOIN message_set_sub_state AS msss
ON msss.list_id = m.list_id
LEFT JOIN set_sub_state AS ses
ON = msss.set_sub_state_id
LEFT JOIN message_waiting AS mwai
ON mwai.list_id = m.list_id
LEFT JOIN waiting AS wai
ON = mwai.waiting_id
WHERE m.list_id = ?)
(SELECT AS id, AS message_last_group_name, AS message_last_service_name, AS message_last_set_state_name, AS message_last_set_sub_state_name, AS message_waiting_name
FROM message AS m
LEFT JOIN message_groups AS msgm
ON msgm.message_id = m.list_id
LEFT JOIN groups AS grou
ON = msgm.last_group_id
LEFT JOIN message_services AS msgs
ON msgs.message_id = m.list_id
LEFT JOIN services AS serm
ON = msgs.last_service_id
LEFT JOIN message_set_state AS msge
ON msge.message_id = m.list_id
LEFT JOIN set_state AS sest
ON = msge.last_set_state_id
LEFT JOIN message_set_sub_state AS mtgs
ON mtgs.message_id = m.list_id
LEFT JOIN set_sub_state AS setm
ON = mtgs.last_set_sub_state_id
LEFT JOIN message_waiting AS mwgs
ON mwgs.message_id = m.list_id
LEFT JOIN waiting AS waim
ON = mwgs.last_waiting_id
WHERE m.list_id = ?)
WHERE m.list_id = ? ORDER BY ASC;



Options: ReplyQuote

Written By
how can I connect multiple select queries via UNION ALL.
June 28, 2020 11:42PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.