-1

I have been reading about the difference between UNIONS and JOINS and believe UNION is what I need for my solution, however I am unable to get this working as I think it's intended.

I've been looking on some other SO articles, including this one. What is the difference between JOIN and UNION?

According to some of the detailed answers in that link, UNION would combine two queries together to form one dataset.

My scenario is that I have a sales database showing number of sales, and at what sales level. I have two such tables with similar information that need to come together to form a complete set.

Table 1: North Side

Item    | Type      | SalesLevel
--------------------------------
John    | Apple     | 1
John    | Pear      | 2
Craig   | Apple     | 1
Dylan   | Apple     | 1

Table 2: South Side

Item    | Type      | SalesLevel
--------------------------------
John    | Apple     | 1
John    | Pear      | 1
Craig   | Apple     | 1
Dylan   | Apple     | 2

As you can see the tables contain the same columns, and my expected results needs to show only the item for each individual where the Type has the highest SalesLevel.

The results that I am after would then look like the following. 

Item    |  Type   |  SalesLevel
--------------------------------
John    |  Apple  | 1
John    |  Pear   | 2
Craig   |  Apple  | 1
Dylan   |  Apple  | 2

When I run my query, I am only getting the Max value per Select statement. I can see 'why' this might happen with the construct of the query, but would have thought that the UNION would have joined these two select statements into one query first? I'm looking for some assistance on how to better construct this query to obtain the expected results.

My query so far

Select Name, Item, MAX(SalesLevel) 
FROM NorthSide 
Group By Name, Item
UNION 
Select Name,
Item, MAX(SalesLevel) 
FROM SouthSide 
Group By Name, Item
Dale K
  • 25,246
  • 15
  • 42
  • 71
Stephen85
  • 250
  • 1
  • 15

4 Answers4

1

UNION will simply a SET operation which combines two resultsets into one, after removing duplicates. Read more about UNION

In your case, you need to get the maximum Saleslevel, in the two sets. You can achieve the same in the below way

SELECT Item, Type, MAX(SalesLevel)
FROM
(
SELECT Item, Type, SalesLevel
FROM North_rs
UNION 
SELECT Item, Type, SalesLevel
FROM South_rs
)
GROUP BY Item, Type
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
0

You may achieve this by using JOIN or UNION, it just depend on your approach.

By Using UNION

  Select Name, Item, Max(SalesLevel) from (
  Select Name, Item, SalesLevel
  FROM NorthSide 
  UNION ALL
  Select Name, Item, SalesLevel
  FROM SouthSide ) as tab 
  group by name , item

By using JOIN

 Select S.Name, S.Item, 
 case when N.SalesLevel > S.SalesLevel 
 then N.SalesLevel 
 else S.SalesLevel end as SalesLevel 
 from NorthSide as N inner join SouthSide as S 
 on N.Name=S.Name and N.Item=S.Item
DarkRob
  • 3,843
  • 1
  • 10
  • 27
0

You can use this query

use Full Join so you get items that exist in one table but not the other

also use Isnull(X,Y) to get results from both tables

I haven't test the code, so it may need some fixes

Select ISNULL(N.Name, S.Name) AS Name, ISNULL(N.Item, S.Item) AS Item, MAX(SalesLevel) 
FROM NorthSide N
FULL JOIN SouthSide S ON N.Name = S.Name AND N.Item = S.Item
Group By ISNULL(N.Name, S.Name), ISNULL(N.Item, S.Item)
asmgx
  • 7,328
  • 15
  • 82
  • 143
0

if you need some combined from 2 tables with the same structure I think "UNION" is the most appropriate way I already test on my local computer with this query

    SELECT pp.Item, pp.Type, MAX(pp.SalesLevel)
FROM
(
SELECT Item, Type, SalesLevel
FROM North_side
UNION ALL
SELECT Item, Type, SalesLevel
FROM South_side
) pp
GROUP BY pp.Item, pp.type

if you need some condition after grouping you can add "HAVING" and add some "aggregate" condition

enter image description here

febry
  • 27
  • 7