CODEWITHSUNDEEP
sqloracle-databasejoin
AAA
AAA

Reputation: 2450

Which Oracle Join to figure out complex query

I have two Oracle tables that I am looking to join but cannot think of the correct join to use.

Sales_Summary

Product Sales   Category1   Category2   Category3   Category4
123     $100    1           0           2           0
123     $200    2           0           2           0
456     $500    1           0           4           0
456     $400    5           2           4           8

User_Access

User    Category    CategoryNum
213     1           1
213     2           3

I tried doing a left join:

select sum(sales) from sales_summary ss
left join user_access ua
on (ss.category1 = ua.category where categorynum = 1)
or (ss.category2 = ua.category where categorynum = 2)
or (ss.category3 = ua.category where categorynum = 3)
or (ss.category4 = ua.category where categorynum = 4)

But it seems to duplicate results when a user has access to multiple categories.

With the SQL above I am hoping that for user 213 it shows total sales of $800 across all products based on their categories.

Category1/Category2/Category4/Category4 = distinct sales categories and are equal to CategoryNum

The Category value is kind of like a part of the category that they might cover (territory?).

Upvotes: 1

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Getting a value of $800 with a join is going to be hard. You can get the value using exists:

SELECT SUM("Sales")
FROM  sales_summary ss
WHERE EXISTS (SELECT 1
              FROM user_access ua
              WHERE (ss."Category1" = ua."Category" AND "CategoryNum" = 1) OR
                    (ss."Category2" = ua."Category" AND "CategoryNum" = 2) OR
                    (ss."Category3" = ua."Category" AND "CategoryNum" = 3) OR
                    (ss."Category4" = ua."Category" AND "CategoryNum" = 4)
              )

The problem with this approach is that you don't get to specify the user. You can specify one user with a where clause in the subquery. Summarizing for multiple users will be hard.

Upvotes: 2

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

You almost got it right. Just replace the where by and -- and you need a group by clause in order to use the aggregate function SUM (assuming you might have several users):

SELECT "User", SUM("Sales")
FROM  sales_summary ss
JOIN user_access ua
ON (ss."Category1" = ua."Category" AND "CategoryNum" = 1)
OR (ss."Category2" = ua."Category" AND "CategoryNum" = 2)
OR (ss."Category3" = ua."Category" AND "CategoryNum" = 3)
OR (ss."Category4" = ua."Category" AND "CategoryNum" = 4)
GROUP BY "User"

See http://sqlfiddle.com/#!4/73d3e/5

... however, this produces a sum of $900. Maybe you should check your requirements and the logic behind if the expected outcome really was $800...

Please note I've quoted all identifiers here as in your sample data, you used what seems to be case-sensitive column names and one column is named User, as so require to be quoted as this is a reserved keyword. Your mileage may vary though.

Upvotes: 2

Related Questions