Duplicates in Union

Question

I'm new to SQL so this is probably why there is an issue with my query but my problem is that I run the below query and end up with many duplicates on my table.

Both tables have a list of items with the same stock numbers which is why I'm joining the stock number. The issue is that I am getting duplication in my list, same stock number but one row has turnover and days on hire, the other has the rest of the information from the second select minus turnover and days on hire.

Any help would be appreciated!

(SELECT NULL                           AS 'ITEM_ID',
        stock_items.stock_number       AS 'STOCK NUMBER',
        SUM(stock_assign.turnover)     AS 'TURNOVER',
        SUM(stock_assign.days_on_hire) AS 'DAYS ON HIRE',
        NULL                           AS ' CAT',
        NULL                           AS 'ITEM NAME',
        NULL                           AS 'DEPOT LOC',
        NULL                           AS 'DISPOSAL DATE',
        NULL                           AS 'DISPOSAL TYPE',
        NULL                           AS 'DATE CREATED',
        NULL                           AS 'CREATED BY',
        NULL                           AS 'COST PRICE'
 FROM   stock_assign
        left join stock_items
               ON stock_assign.stock_number = stock_items.stock_number
        left join depots
               ON depot_id = depots.dpt_id
 GROUP  BY stock_items.stock_number,
           depots.dpt_name)
UNION
(SELECT stock_items.item_id         AS 'ITEM_ID',
        stock_items.stock_number    AS 'STOCK NUMBER',
        NULL                        AS 'TURNOVER',
        NULL                        AS 'DAYS ON HIRE',
        stock_items.equipment_class AS ' CAT',
        stock_items.name            AS 'ITEM NAME',
        depots.dpt_name             AS 'DEPOT LOC',
        stock_items.disposal_date   AS 'DISPOSAL DATE',
        stock_items.disposal_type   AS 'DISPOSAL TYPE',
        stock_items.date_available  AS 'DATE CREATED',
        users.usr_name              AS 'CREATED BY',
        fixed_assets.cost_amount    AS 'COST PRICE'
 FROM   stock_items
        left join users
               ON users.usr_id = stock_items.userid_created
        left join depots
               ON stock_items.depot_id_located_at = depots.dpt_id
        left join fixed_assets
               ON stock_items.item_id = fixed_assets.stock_item_id)
ORDER  BY 'STOCK NUMBER' ASC

Edward Comeau · Accepted Answer · 2016-10-27T16:28:11.440

The behavior of UNION is to return unique rows when every column in the unioned datasets match.

You have two selects which are apparently always going to give different results due to the nulls assigned for many columns.

Perhaps if you re-wrote this as a JOIN of two datasets you would get the result that you want.

Assuming you have a single row on each cte which you want to join below is an example;

WITH cte_Assignment AS
(
    SELECT
        stock_items.stock_number       AS 'STOCK NUMBER',
        SUM(stock_assign.turnover)     AS 'TURNOVER',
        SUM(stock_assign.days_on_hire) AS 'DAYS ON HIRE'
    FROM stock_assign
    LEFT JOIN stock_items
        ON stock_assign.stock_number = stock_items.stock_number
    LEFT JOIN depots
        ON depot_id = depots.dpt_id
    GROUP BY
        stock_items.stock_number
)

, cte_Item AS
(
    SELECT
        stock_items.item_id         AS 'ITEM_ID',
        stock_items.stock_number    AS 'STOCK NUMBER',
        stock_items.equipment_class AS ' CAT',
        stock_items.name            AS 'ITEM NAME',
        depots.dpt_name             AS 'DEPOT LOC',
        stock_items.disposal_date   AS 'DISPOSAL DATE',
        stock_items.disposal_type   AS 'DISPOSAL TYPE',
        stock_items.date_available  AS 'DATE CREATED',
        users.usr_name              AS 'CREATED BY',
        fixed_assets.cost_amount    AS 'COST PRICE'
    FROM stock_items
    LEFT JOIN users
        ON users.usr_id = stock_items.userid_created
    LEFT JOIN depots
        ON stock_items.depot_id_located_at = depots.dpt_id
    LEFT JOIN fixed_assets
        ON stock_items.item_id = fixed_assets.stock_item_id
)

SELECT
    item.[ITEM_ID],
    item.[STOCK NUMBER],
    assignment.[TURNOVER],
    assignment.[DAYS ON HIRE],
    item.[ CAT],
    item.[ITEM NAME],
    item.[DEPOT LOC],
    item.[DISPOSAL DATE],
    item.[DISPOSAL TYPE],
    item.[DATE CREATED],
    item.[CREATED BY],
    item.[COST PRICE]
FROM cte_Assignment assignment
INNER JOIN cte_Item item
    on item.[STOCK NUMBER] = assignment.[STOCK NUMBER]

ORDER  BY item.[STOCK NUMBER] ASC

Thanks for your help - this looks like what I need but I am having trouble getting it to recognise the fields in cte_Item, it doesn't seem to recognise the CTE reference although it does for assignments. I've tried ,cte_Item and ,cte_Item AS, I'm using SQL Server 2012 Express if that has anything to do with it? It is worth noting I had to add the AS to the first statement WITH cte_Assignemnt AS — Andy00001, Oct 27 '16 at 14:01
I've updated with the missing `AS` entries. I'm looking but can't see a further issue yet. **Correction**, found two typos and corrected. — Edward Comeau, Oct 27 '16 at 14:09
ok cool - I still get Invalid object name 'cte.Item' maybe just something with the syntax somewhere although I looked up how to use CTE and this seems correct — Andy00001, Oct 27 '16 at 14:18
you mention `cte.Item` with the full stop, was that a typo in your comment? Still scanning for errors.... — Edward Comeau, Oct 27 '16 at 14:21
yes sorry I posted another comment it was a typo it works now although I was still getting the duplicates until I removed depots from the Group by, just checking the data at the mo — Andy00001, Oct 27 '16 at 14:38

score 0 · Answer 2 · answered Oct 27 '16 at 13:18

Another option you have is to wrap the query you have now as a sub-query.

SELECT MAX(ITEM_ID) as ITEM_ID, SUM(TURNOVER) 
FROM 
(SELECT NULL                           AS 'ITEM_ID',
    stock_items.stock_number       AS 'STOCK NUMBER',
    SUM(stock_assign.turnover)     AS 'TURNOVER',
    SUM(stock_assign.days_on_hire) AS 'DAYS ON HIRE',
    NULL                           AS ' CAT',
    NULL                           AS 'ITEM NAME',
    NULL                           AS 'DEPOT LOC',
    NULL                           AS 'DISPOSAL DATE',
    NULL                           AS 'DISPOSAL TYPE',
    NULL                           AS 'DATE CREATED',
    NULL                           AS 'CREATED BY',
    NULL                           AS 'COST PRICE'
 FROM   stock_assign
    left join stock_items
           ON stock_assign.stock_number = stock_items.stock_number
    left join depots
           ON depot_id = depots.dpt_id
GROUP  BY stock_items.stock_number,
       depots.dpt_name)
UNION
(SELECT stock_items.item_id         AS 'ITEM_ID',
    stock_items.stock_number    AS 'STOCK NUMBER',
    NULL                        AS 'TURNOVER',
    NULL                        AS 'DAYS ON HIRE',
    stock_items.equipment_class AS ' CAT',
    stock_items.name            AS 'ITEM NAME',
    depots.dpt_name             AS 'DEPOT LOC',
    stock_items.disposal_date   AS 'DISPOSAL DATE',
    stock_items.disposal_type   AS 'DISPOSAL TYPE',
    stock_items.date_available  AS 'DATE CREATED',
    users.usr_name              AS 'CREATED BY',
    fixed_assets.cost_amount    AS 'COST PRICE'
FROM   stock_items
    left join users
           ON users.usr_id = stock_items.userid_created
    left join depots
           ON stock_items.depot_id_located_at = depots.dpt_id
    left join fixed_assets
           ON stock_items.item_id = fixed_assets.stock_item_id)
ORDER  BY 'STOCK NUMBER' ASC) a
GROUP BY a.[Stock Number]

You would then need to group by all the similar columns in the 2 queries ie. Stock Number. Where you have decimal values in both queries use SUM and if you have a null in the one query and a value in the other use MAX.

If you do this correctly you will end up with a single grouped row for each stock number.

As a side note, try to stay clear of using a space in your column names, rather replace it with a _

Duplicates in Union

2 Answers2