how can I connect multiple select queries via UNION ALL.
Posted by: Kiumars Jahandel
Date: June 28, 2020 11:42PM
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 m.id 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,
MOD(HOUR(TIMEDIFF(m.created, CURRENT_TIMESTAMP())), 24) AS hour,
MINUTE(TIMEDIFF(m.created, CURRENT_TIMESTAMP())) AS minute,
SECOND(TIMEDIFF(m.created, CURRENT_TIMESTAMP())) AS second,
ud.external_company AS external_company, ud.country AS country, ud.city AS city, ud.post_code AS post_code, ud.street_and_house_number AS street_and_house_number,
ud.floor AS floor, ud.room 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,
gr.id AS group_id, gr.name AS group_name,
gra.name AS assigned_group_name,
l.id AS location_id, l.name AS location_name,
lo.id AS location_id, lo.name AS location_name,
ser.id AS service_category_id, ser.name AS service_category_name,
cat.id AS category_id, cat.name AS category_name,
apl.id AS application_id, apl.name AS application_name,
das.id AS database_server_id, das.name AS database_server_name,
enua.id AS end_user_asset_id, enua.name AS end_user_asset_name,
netw.id AS network_id, netw.name AS network_name,
peri.id AS peripheral_system_id, peri.name AS peripheral_system_name,
serv.id AS server_id, serv.name AS server_name,
isr.id AS is_resolved_id, isr.name AS is_resolved_name, isr.icon AS is_resolved_icon, isr.color AS is_resolved_color,
imp.id AS impact_id, imp.name AS impact_name,
ctps.id AS contact_type_id, ctps.name AS contact_type_name,
pri.id AS priority_id, pri.name AS priority_name,
prop.id AS priority_problem_id, prop.name AS priority_problem_name,
urg.id AS urgency_id, urg.name urgency_name,
sor.id AS source_id, sor.name AS source_name,
im.id AS image_id, im.name AS name, im.image AS image, im.size AS size, im.created AS image_created,
u.company AS company, u.firstname AS firstname, u.lastname AS lastname, u.email AS email, u.role AS role, u.color AS color,
u.created AS user_created, u.updated AS user_updated,
ur.email AS requested_for_email, CONCAT(ur.firstname, " ", ur.lastname) AS requested_for, ur.company AS requested_for_company,
uo.email AS opened_by_email, CONCAT(uo.firstname, " ", uo.lastname) AS opened_by, uo.company AS opened_by_company,
ure.email AS receiver_email, CONCAT(ure.firstname, " ", ure.lastname) AS receiver_member, ure.company AS receiver_company,
uas.email AS assigned_email, CONCAT(uas.firstname, " ", uas.lastname) AS assigned_member, uas.company AS assigned_company
FROM message AS m
LEFT JOIN user AS u
ON u.id = m.user_id
LEFT JOIN user AS ure
ON ure.id = m.receiver_id
LEFT JOIN user_details AS ud
ON ud.user_id = m.user_id
LEFT JOIN images AS im
ON im.id = m.image_id
LEFT JOIN user AS ur
ON ur.id = m.requested_for_id
LEFT JOIN user AS uo
ON uo.id = m.opened_by_id
LEFT JOIN user AS uas
ON uas.id = 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 l.id = usl.location_id
LEFT JOIN message_locations AS msl
ON msl.list_id = m.list_id
LEFT JOIN locations AS lo
ON lo.id = msl.location_id
LEFT JOIN user_groups AS usg
ON usg.user_id = u.id
LEFT JOIN groups AS gr
ON gr.id = usg.group_id
LEFT JOIN user_groups AS usga
ON usga.user_id = m.assigned_id
LEFT JOIN groups AS gra
ON gra.id = usga.group_id
LEFT JOIN message_service_category AS mser
ON mser.list_id = m.list_id
LEFT JOIN service_category AS ser
ON ser.id = mser.service_category_id
LEFT JOIN message_category AS mca
ON mca.list_id = m.list_id
LEFT JOIN category AS cat
ON cat.id = 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 apl.id = capl.application_id
LEFT JOIN database_server AS das
ON das.id = cdas.database_server_id
LEFT JOIN end_user_asset AS enua
ON enua.id = cnua.end_user_asset_id
LEFT JOIN network AS netw
ON netw.id = cane.network_id
LEFT JOIN peripheral_system AS peri
ON peri.id = cpri.peripheral_system_id
LEFT JOIN server AS serv
ON serv.id = cerv.server_id
LEFT JOIN message_impact AS mimp
ON mimp.list_id = m.list_id
LEFT JOIN impact AS imp
ON imp.id = mimp.impact_id
LEFT JOIN message_contact_types AS mctp
ON mctp.list_id = m.list_id
LEFT JOIN contact_types AS ctps
ON ctps.id = mctp.contact_type_id
LEFT JOIN message_priority AS mspr
ON mspr.list_id = m.list_id
LEFT JOIN priority AS pri
ON pri.id = mspr.priority_id
LEFT JOIN message_priority_problem AS mprp
ON mprp.list_id = m.list_id
LEFT JOIN priority_problem AS prop
ON prop.id = 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 isr.id = misr.is_resolved_id
LEFT JOIN message_urgency AS meur
ON meur.list_id = m.list_id
LEFT JOIN urgency AS urg
ON urg.id = meur.urgency_id
LEFT JOIN message_sources AS meso
ON meso.list_id = m.list_id
LEFT JOIN sources AS sor
ON sor.id = meso.source_id
(SELECT m.id AS id,
gro.id AS message_group_id, gro.name AS message_group_name,
sers.id AS service_id, sers.name AS service_name,
sets.id AS set_state_id, sets.name AS set_state_name, sets.color AS set_state_color, sets.icon AS set_state_icon,
ses.id AS set_sub_state_id, ses.name AS set_sub_state_name,
wai.id AS waiting_id, wai.name 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 gro.id = msg.group_id
LEFT JOIN message_services AS mese
ON mese.list_id = m.list_id
LEFT JOIN services AS sers
ON sers.id = mese.service_id
LEFT JOIN message_set_state AS mess
ON mess.list_id = m.list_id
LEFT JOIN set_state AS sets
ON sets.id = 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 ses.id = msss.set_sub_state_id
LEFT JOIN message_waiting AS mwai
ON mwai.list_id = m.list_id
LEFT JOIN waiting AS wai
ON wai.id = mwai.waiting_id
WHERE m.list_id = ?)
UNION ALL
(SELECT m.id AS id,
grou.name AS message_last_group_name,
serm.name AS message_last_service_name,
sest.name AS message_last_set_state_name,
setm.name AS message_last_set_sub_state_name,
waim.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 grou.id = msgm.last_group_id
LEFT JOIN message_services AS msgs
ON msgs.message_id = m.list_id
LEFT JOIN services AS serm
ON serm.id = 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 sest.id = 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 setm.id = 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 waim.id = mwgs.last_waiting_id
WHERE m.list_id = ?)
WHERE m.list_id = ? ORDER BY m.id ASC;
`;
db.query(sqlData,[list_id]).then((rows)=>{
res.send(rows);
});
};
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 m.id 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,
MOD(HOUR(TIMEDIFF(m.created, CURRENT_TIMESTAMP())), 24) AS hour,
MINUTE(TIMEDIFF(m.created, CURRENT_TIMESTAMP())) AS minute,
SECOND(TIMEDIFF(m.created, CURRENT_TIMESTAMP())) AS second,
ud.external_company AS external_company, ud.country AS country, ud.city AS city, ud.post_code AS post_code, ud.street_and_house_number AS street_and_house_number,
ud.floor AS floor, ud.room 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,
gr.id AS group_id, gr.name AS group_name,
gra.name AS assigned_group_name,
l.id AS location_id, l.name AS location_name,
lo.id AS location_id, lo.name AS location_name,
ser.id AS service_category_id, ser.name AS service_category_name,
cat.id AS category_id, cat.name AS category_name,
apl.id AS application_id, apl.name AS application_name,
das.id AS database_server_id, das.name AS database_server_name,
enua.id AS end_user_asset_id, enua.name AS end_user_asset_name,
netw.id AS network_id, netw.name AS network_name,
peri.id AS peripheral_system_id, peri.name AS peripheral_system_name,
serv.id AS server_id, serv.name AS server_name,
isr.id AS is_resolved_id, isr.name AS is_resolved_name, isr.icon AS is_resolved_icon, isr.color AS is_resolved_color,
imp.id AS impact_id, imp.name AS impact_name,
ctps.id AS contact_type_id, ctps.name AS contact_type_name,
pri.id AS priority_id, pri.name AS priority_name,
prop.id AS priority_problem_id, prop.name AS priority_problem_name,
urg.id AS urgency_id, urg.name urgency_name,
sor.id AS source_id, sor.name AS source_name,
im.id AS image_id, im.name AS name, im.image AS image, im.size AS size, im.created AS image_created,
u.company AS company, u.firstname AS firstname, u.lastname AS lastname, u.email AS email, u.role AS role, u.color AS color,
u.created AS user_created, u.updated AS user_updated,
ur.email AS requested_for_email, CONCAT(ur.firstname, " ", ur.lastname) AS requested_for, ur.company AS requested_for_company,
uo.email AS opened_by_email, CONCAT(uo.firstname, " ", uo.lastname) AS opened_by, uo.company AS opened_by_company,
ure.email AS receiver_email, CONCAT(ure.firstname, " ", ure.lastname) AS receiver_member, ure.company AS receiver_company,
uas.email AS assigned_email, CONCAT(uas.firstname, " ", uas.lastname) AS assigned_member, uas.company AS assigned_company
FROM message AS m
LEFT JOIN user AS u
ON u.id = m.user_id
LEFT JOIN user AS ure
ON ure.id = m.receiver_id
LEFT JOIN user_details AS ud
ON ud.user_id = m.user_id
LEFT JOIN images AS im
ON im.id = m.image_id
LEFT JOIN user AS ur
ON ur.id = m.requested_for_id
LEFT JOIN user AS uo
ON uo.id = m.opened_by_id
LEFT JOIN user AS uas
ON uas.id = 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 l.id = usl.location_id
LEFT JOIN message_locations AS msl
ON msl.list_id = m.list_id
LEFT JOIN locations AS lo
ON lo.id = msl.location_id
LEFT JOIN user_groups AS usg
ON usg.user_id = u.id
LEFT JOIN groups AS gr
ON gr.id = usg.group_id
LEFT JOIN user_groups AS usga
ON usga.user_id = m.assigned_id
LEFT JOIN groups AS gra
ON gra.id = usga.group_id
LEFT JOIN message_service_category AS mser
ON mser.list_id = m.list_id
LEFT JOIN service_category AS ser
ON ser.id = mser.service_category_id
LEFT JOIN message_category AS mca
ON mca.list_id = m.list_id
LEFT JOIN category AS cat
ON cat.id = 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 apl.id = capl.application_id
LEFT JOIN database_server AS das
ON das.id = cdas.database_server_id
LEFT JOIN end_user_asset AS enua
ON enua.id = cnua.end_user_asset_id
LEFT JOIN network AS netw
ON netw.id = cane.network_id
LEFT JOIN peripheral_system AS peri
ON peri.id = cpri.peripheral_system_id
LEFT JOIN server AS serv
ON serv.id = cerv.server_id
LEFT JOIN message_impact AS mimp
ON mimp.list_id = m.list_id
LEFT JOIN impact AS imp
ON imp.id = mimp.impact_id
LEFT JOIN message_contact_types AS mctp
ON mctp.list_id = m.list_id
LEFT JOIN contact_types AS ctps
ON ctps.id = mctp.contact_type_id
LEFT JOIN message_priority AS mspr
ON mspr.list_id = m.list_id
LEFT JOIN priority AS pri
ON pri.id = mspr.priority_id
LEFT JOIN message_priority_problem AS mprp
ON mprp.list_id = m.list_id
LEFT JOIN priority_problem AS prop
ON prop.id = 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 isr.id = misr.is_resolved_id
LEFT JOIN message_urgency AS meur
ON meur.list_id = m.list_id
LEFT JOIN urgency AS urg
ON urg.id = meur.urgency_id
LEFT JOIN message_sources AS meso
ON meso.list_id = m.list_id
LEFT JOIN sources AS sor
ON sor.id = meso.source_id
(SELECT m.id AS id,
gro.id AS message_group_id, gro.name AS message_group_name,
sers.id AS service_id, sers.name AS service_name,
sets.id AS set_state_id, sets.name AS set_state_name, sets.color AS set_state_color, sets.icon AS set_state_icon,
ses.id AS set_sub_state_id, ses.name AS set_sub_state_name,
wai.id AS waiting_id, wai.name 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 gro.id = msg.group_id
LEFT JOIN message_services AS mese
ON mese.list_id = m.list_id
LEFT JOIN services AS sers
ON sers.id = mese.service_id
LEFT JOIN message_set_state AS mess
ON mess.list_id = m.list_id
LEFT JOIN set_state AS sets
ON sets.id = 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 ses.id = msss.set_sub_state_id
LEFT JOIN message_waiting AS mwai
ON mwai.list_id = m.list_id
LEFT JOIN waiting AS wai
ON wai.id = mwai.waiting_id
WHERE m.list_id = ?)
UNION ALL
(SELECT m.id AS id,
grou.name AS message_last_group_name,
serm.name AS message_last_service_name,
sest.name AS message_last_set_state_name,
setm.name AS message_last_set_sub_state_name,
waim.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 grou.id = msgm.last_group_id
LEFT JOIN message_services AS msgs
ON msgs.message_id = m.list_id
LEFT JOIN services AS serm
ON serm.id = 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 sest.id = 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 setm.id = 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 waim.id = mwgs.last_waiting_id
WHERE m.list_id = ?)
WHERE m.list_id = ? ORDER BY m.id ASC;
`;
db.query(sqlData,[list_id]).then((rows)=>{
res.send(rows);
});
};
Subject
Written By
Posted
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.