CODEWITHSUNDEEP
sql
morne
morne

Reputation: 4189

UNION on 2 queries with one table difference

Id like to merge or UNION two queries

The query result columns are of the same type and size but one table differs in each. Can it be done?

The first is from the Absent table the second form the Attend Table. These two tables are basically exactly the same( dont know why there has to be 2 tables if 1 would have sufficed)

SELECT DISTINCT dmg.dmg_FirstName,
        dmg.dmg_Surname,
        lnk.lnk_ID,
        dlk.dlk_AssessDate,
        dmg.dmg_Sex,
        trn.ScotHealthboard
FROM    DAILY_LINK dlk
JOIN    Absent ab ON ab.ScotRefID = dlk.dlk_ID
JOIN    Link lnk ON lnk.lnk_ID = ab.Person_ID
JOIN    Demographic dmg ON dmg.dmg_ID = lnk.lnk_dmgID
JOIN    Training trn ON trn.Trn_ID = ab.Training_ID
WHERE   dlk.dlk_AssessDate >= '2015/01/01' AND dlk.dlk_AssessDate <= '2015/12/01';
UNION
SELECT DISTINCT dmg.dmg_FirstName,
        dmg.dmg_Surname,
        lnk.lnk_ID,
        dlk.dlk_AssessDate,
        dmg.dmg_Sex,
        trn.ScotHealthboard
FROM    DAILY_LINK dlk
JOIN    Attend at ON at.ScotRefID = dlk.dlk_ID
JOIN    Link lnk ON lnk.lnk_ID = at.Person_ID
JOIN    Demographic dmg ON dmg.dmg_ID = lnk.lnk_dmgID
JOIN    Training trn ON trn.Trn_ID = at.Training_ID
WHERE   dlk.dlk_AssessDate >= '2015/01/01' AND dlk.dlk_AssessDate <= '2015/12/01';

Upvotes: 0

Views: 40

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15140

I would suspect the ; just before union is causing an error. If you remove it, it should run fine.

SELECT DISTINCT dmg.dmg_FirstName,
        dmg.dmg_Surname,
        lnk.lnk_ID,
        dlk.dlk_AssessDate,
        dmg.dmg_Sex,
        trn.ScotHealthboard
FROM    DAILY_LINK dlk
JOIN    Absent ab ON ab.ScotRefID = dlk.dlk_ID
JOIN    Link lnk ON lnk.lnk_ID = ab.Person_ID
JOIN    Demographic dmg ON dmg.dmg_ID = lnk.lnk_dmgID
JOIN    Training trn ON trn.Trn_ID = ab.Training_ID
WHERE   dlk.dlk_AssessDate >= '2015/01/01' AND dlk.dlk_AssessDate <= '2015/12/01'
UNION
SELECT DISTINCT dmg.dmg_FirstName,
        dmg.dmg_Surname,
        lnk.lnk_ID,
        dlk.dlk_AssessDate,
        dmg.dmg_Sex,
        trn.ScotHealthboard
FROM    DAILY_LINK dlk
JOIN    Attend at ON at.ScotRefID = dlk.dlk_ID
JOIN    Link lnk ON lnk.lnk_ID = at.Person_ID
JOIN    Demographic dmg ON dmg.dmg_ID = lnk.lnk_dmgID
JOIN    Training trn ON trn.Trn_ID = at.Training_ID
WHERE   dlk.dlk_AssessDate >= '2015/01/01' AND dlk.dlk_AssessDate <= '2015/12/01';

Upvotes: 2

Related Questions