Aggregation, joining, and filtering with many-to-many table
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
Let's say i have a database of customers who buy materials that are "applicable" to random objects. For example, John buys $10 of "Material X" that is applicable to a car and a house.
Customers
+----+-------+
| ID | Name |
+----+-------+
| 1 | John |
| 2 | Larry |
+----+-------+
Orders
+---------+------------+-------+----------+
| OrderID | CustomerID | Sales | Material |
+---------+------------+-------+----------+
| 1 | 1 | 10 | x |
| 2 | 1 | 15 | x |
| 3 | 1 | 6 | y |
| 4 | 2 | 3 | x |
| 5 | 2 | 25 | y |
+---------+------------+-------+----------+
My Materials table originally looked like this
+----------+-------------------------+
| Material | Applicability |
+----------+-------------------------+
| x | car, house, plane, bike |
| y | car, bike |
+----------+-------------------------+
When I need to display what materials John buys and which objects that material is applicable to, my query is this.
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John'
GROUP BY ID, Name, Material, Applicability
The result
+----+------+--------------+----------+-------------------------+
| ID | Name | Total Sales | Material | Applicability |
+----+------+--------------+----------+-------------------------+
| 1 | John | 25 | x | car, house, plane, bike |
| 1 | John | 6 | y | car, bike |
+----+------+--------------+----------+-------------------------+
The comma separated values (i know it violates many rules) was convenient, because when parsing the applicability i could simply split the string by commas and then i had a list of applicability objects.
Now it's been decided to normalize the Materials table. The table relationship is many to many. A material can be applicable to many objects, and objects can contain many materials.
+----------+---------------+
| Material | Applicability |
+----------+---------------+
| x | car |
| x | house |
| x | plane |
| x | bike |
| y | car |
| y | bike |
+----------+---------------+
This normalization has disrupted my existing query, it causes the sum(sales) result to be a multiple of however many objects the material is applicable to.
Example.
+----+------+-------------+----------+---------------+
| ID | Name | Total Sales | Material | Applicability |
+----+------+-------------+----------+---------------+
| 1 | John | 25 | x | car |
| 1 | John | 25 | x | house |
| 1 | John | 25 | x | plane |
| 1 | John | 25 | x | bike |
| 1 | John | 6 | y | car |
| 1 | John | 6 | y | bike |
+----+------+-------------+----------+---------------+
Now it looks John has bought $100 of material x, when he has really only bought $25. I need to show the user John's purchase of material x, as well as x's applicability.
The main problem is when i need to find out what John buys, but also filter by applicability.
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY ID, Name, Material, Applicability
If any material is applicable to both car and bike, then the aggregate value sum(sales) will be doubled.
How do i deal with this duplication? I'm open to getting the result set in a different format, I just want the aggregation results to be correct.
sql-server
asked Aug 3 '16 at 19:17
Eric GuanEric Guan
1213
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Let's say i have a database of customers who buy materials that are "applicable" to random objects. For example, John buys $10 of "Material X" that is applicable to a car and a house.
Customers
+----+-------+
| ID | Name |
+----+-------+
| 1 | John |
| 2 | Larry |
+----+-------+
Orders
+---------+------------+-------+----------+
| OrderID | CustomerID | Sales | Material |
+---------+------------+-------+----------+
| 1 | 1 | 10 | x |
| 2 | 1 | 15 | x |
| 3 | 1 | 6 | y |
| 4 | 2 | 3 | x |
| 5 | 2 | 25 | y |
+---------+------------+-------+----------+
My Materials table originally looked like this
+----------+-------------------------+
| Material | Applicability |
+----------+-------------------------+
| x | car, house, plane, bike |
| y | car, bike |
+----------+-------------------------+
When I need to display what materials John buys and which objects that material is applicable to, my query is this.
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John'
GROUP BY ID, Name, Material, Applicability
The result
+----+------+--------------+----------+-------------------------+
| ID | Name | Total Sales | Material | Applicability |
+----+------+--------------+----------+-------------------------+
| 1 | John | 25 | x | car, house, plane, bike |
| 1 | John | 6 | y | car, bike |
+----+------+--------------+----------+-------------------------+
The comma separated values (i know it violates many rules) was convenient, because when parsing the applicability i could simply split the string by commas and then i had a list of applicability objects.
Now it's been decided to normalize the Materials table. The table relationship is many to many. A material can be applicable to many objects, and objects can contain many materials.
+----------+---------------+
| Material | Applicability |
+----------+---------------+
| x | car |
| x | house |
| x | plane |
| x | bike |
| y | car |
| y | bike |
+----------+---------------+
This normalization has disrupted my existing query, it causes the sum(sales) result to be a multiple of however many objects the material is applicable to.
Example.
+----+------+-------------+----------+---------------+
| ID | Name | Total Sales | Material | Applicability |
+----+------+-------------+----------+---------------+
| 1 | John | 25 | x | car |
| 1 | John | 25 | x | house |
| 1 | John | 25 | x | plane |
| 1 | John | 25 | x | bike |
| 1 | John | 6 | y | car |
| 1 | John | 6 | y | bike |
+----+------+-------------+----------+---------------+
Now it looks John has bought $100 of material x, when he has really only bought $25. I need to show the user John's purchase of material x, as well as x's applicability.
The main problem is when i need to find out what John buys, but also filter by applicability.
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY ID, Name, Material, Applicability
If any material is applicable to both car and bike, then the aggregate value sum(sales) will be doubled.
How do i deal with this duplication? I'm open to getting the result set in a different format, I just want the aggregation results to be correct.
sql-server
asked Aug 3 '16 at 19:17
Eric GuanEric Guan
1213
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Do you need to return applicability as well as filter by it? By removing it, you can use a SELECT DISTINCT.
– Haris Khan
Aug 3 '16 at 19:34
add a comment |
Let's say i have a database of customers who buy materials that are "applicable" to random objects. For example, John buys $10 of "Material X" that is applicable to a car and a house.
Customers
+----+-------+
| ID | Name |
+----+-------+
| 1 | John |
| 2 | Larry |
+----+-------+
Orders
+---------+------------+-------+----------+
| OrderID | CustomerID | Sales | Material |
+---------+------------+-------+----------+
| 1 | 1 | 10 | x |
| 2 | 1 | 15 | x |
| 3 | 1 | 6 | y |
| 4 | 2 | 3 | x |
| 5 | 2 | 25 | y |
+---------+------------+-------+----------+
My Materials table originally looked like this
+----------+-------------------------+
| Material | Applicability |
+----------+-------------------------+
| x | car, house, plane, bike |
| y | car, bike |
+----------+-------------------------+
When I need to display what materials John buys and which objects that material is applicable to, my query is this.
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John'
GROUP BY ID, Name, Material, Applicability
The result
+----+------+--------------+----------+-------------------------+
| ID | Name | Total Sales | Material | Applicability |
+----+------+--------------+----------+-------------------------+
| 1 | John | 25 | x | car, house, plane, bike |
| 1 | John | 6 | y | car, bike |
+----+------+--------------+----------+-------------------------+
The comma separated values (i know it violates many rules) was convenient, because when parsing the applicability i could simply split the string by commas and then i had a list of applicability objects.
Now it's been decided to normalize the Materials table. The table relationship is many to many. A material can be applicable to many objects, and objects can contain many materials.
+----------+---------------+
| Material | Applicability |
+----------+---------------+
| x | car |
| x | house |
| x | plane |
| x | bike |
| y | car |
| y | bike |
+----------+---------------+
This normalization has disrupted my existing query, it causes the sum(sales) result to be a multiple of however many objects the material is applicable to.
Example.
+----+------+-------------+----------+---------------+
| ID | Name | Total Sales | Material | Applicability |
+----+------+-------------+----------+---------------+
| 1 | John | 25 | x | car |
| 1 | John | 25 | x | house |
| 1 | John | 25 | x | plane |
| 1 | John | 25 | x | bike |
| 1 | John | 6 | y | car |
| 1 | John | 6 | y | bike |
+----+------+-------------+----------+---------------+
Now it looks John has bought $100 of material x, when he has really only bought $25. I need to show the user John's purchase of material x, as well as x's applicability.
The main problem is when i need to find out what John buys, but also filter by applicability.
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY ID, Name, Material, Applicability
If any material is applicable to both car and bike, then the aggregate value sum(sales) will be doubled.
How do i deal with this duplication? I'm open to getting the result set in a different format, I just want the aggregation results to be correct.
sql-server
asked Aug 3 '16 at 19:17
Eric GuanEric Guan
1213
Let's say i have a database of customers who buy materials that are "applicable" to random objects. For example, John buys $10 of "Material X" that is applicable to a car and a house.
Customers
+----+-------+
| ID | Name |
+----+-------+
| 1 | John |
| 2 | Larry |
+----+-------+
Orders
+---------+------------+-------+----------+
| OrderID | CustomerID | Sales | Material |
+---------+------------+-------+----------+
| 1 | 1 | 10 | x |
| 2 | 1 | 15 | x |
| 3 | 1 | 6 | y |
| 4 | 2 | 3 | x |
| 5 | 2 | 25 | y |
+---------+------------+-------+----------+
My Materials table originally looked like this
+----------+-------------------------+
| Material | Applicability |
+----------+-------------------------+
| x | car, house, plane, bike |
| y | car, bike |
+----------+-------------------------+
When I need to display what materials John buys and which objects that material is applicable to, my query is this.
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John'
GROUP BY ID, Name, Material, Applicability
The result
+----+------+--------------+----------+-------------------------+
| ID | Name | Total Sales | Material | Applicability |
+----+------+--------------+----------+-------------------------+
| 1 | John | 25 | x | car, house, plane, bike |
| 1 | John | 6 | y | car, bike |
+----+------+--------------+----------+-------------------------+
The comma separated values (i know it violates many rules) was convenient, because when parsing the applicability i could simply split the string by commas and then i had a list of applicability objects.
Now it's been decided to normalize the Materials table. The table relationship is many to many. A material can be applicable to many objects, and objects can contain many materials.
+----------+---------------+
| Material | Applicability |
+----------+---------------+
| x | car |
| x | house |
| x | plane |
| x | bike |
| y | car |
| y | bike |
+----------+---------------+
This normalization has disrupted my existing query, it causes the sum(sales) result to be a multiple of however many objects the material is applicable to.
Example.
+----+------+-------------+----------+---------------+
| ID | Name | Total Sales | Material | Applicability |
+----+------+-------------+----------+---------------+
| 1 | John | 25 | x | car |
| 1 | John | 25 | x | house |
| 1 | John | 25 | x | plane |
| 1 | John | 25 | x | bike |
| 1 | John | 6 | y | car |
| 1 | John | 6 | y | bike |
+----+------+-------------+----------+---------------+
Now it looks John has bought $100 of material x, when he has really only bought $25. I need to show the user John's purchase of material x, as well as x's applicability.
The main problem is when i need to find out what John buys, but also filter by applicability.
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY ID, Name, Material, Applicability
If any material is applicable to both car and bike, then the aggregate value sum(sales) will be doubled.
How do i deal with this duplication? I'm open to getting the result set in a different format, I just want the aggregation results to be correct.
sql-server
sql-server
asked Aug 3 '16 at 19:17
Eric GuanEric Guan
1213
asked Aug 3 '16 at 19:17
Eric GuanEric Guan
1213
asked Aug 3 '16 at 19:17
Eric GuanEric Guan
1213
asked Aug 3 '16 at 19:17
Eric GuanEric Guan
1213
asked Aug 3 '16 at 19:17
Eric GuanEric Guan
1213
1213
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Do you need to return applicability as well as filter by it? By removing it, you can use a SELECT DISTINCT.
– Haris Khan
Aug 3 '16 at 19:34
add a comment |
Do you need to return applicability as well as filter by it? By removing it, you can use a SELECT DISTINCT.
– Haris Khan
Aug 3 '16 at 19:34
Do you need to return applicability as well as filter by it? By removing it, you can use a SELECT DISTINCT.
– Haris Khan
Aug 3 '16 at 19:34
Do you need to return applicability as well as filter by it? By removing it, you can use a SELECT DISTINCT.
– Haris Khan
Aug 3 '16 at 19:34
add a comment |
2 Answers
2
active
oldest
votes
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and applicability = 'car'
GROUP BY ID, Name, Material, Applicability
UNION
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and applicability = 'bike'
GROUP BY ID, Name, Material, Applicability
answered Aug 3 '16 at 19:59
paparazzopaparazzo
4,6621230
add a comment |
if you group by material, you can select the max sales and group_concat the applicability. this will give one value for the sale of material x with a new column that shows the combined, filtered uses. then a subquery will show the correct sum of sales per material (or order, or however else you decide to group).
I added group by OrderID in the subquery since it seems like you're looking for John's lifetime sales?
select ID, Name, sum(Sales), Material, Applicability
(Select ID, Name, max(Sales) as Sales, Material, group_concat(Applicability separator ', ') as Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY OrderID, ID, Name, Material) sub
GROUP BY ID, Name, Material
answered Aug 3 '16 at 22:40
peachpeach
512
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f145799%2faggregation-joining-and-filtering-with-many-to-many-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and applicability = 'car'
GROUP BY ID, Name, Material, Applicability
UNION
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and applicability = 'bike'
GROUP BY ID, Name, Material, Applicability
answered Aug 3 '16 at 19:59
paparazzopaparazzo
4,6621230
add a comment |
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and applicability = 'car'
GROUP BY ID, Name, Material, Applicability
UNION
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and applicability = 'bike'
GROUP BY ID, Name, Material, Applicability
answered Aug 3 '16 at 19:59
paparazzopaparazzo
4,6621230
add a comment |
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and applicability = 'car'
GROUP BY ID, Name, Material, Applicability
UNION
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and applicability = 'bike'
GROUP BY ID, Name, Material, Applicability
answered Aug 3 '16 at 19:59
paparazzopaparazzo
4,6621230
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and applicability = 'car'
GROUP BY ID, Name, Material, Applicability
UNION
Select ID, Name, sum(Sales), Material, Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and applicability = 'bike'
GROUP BY ID, Name, Material, Applicability
answered Aug 3 '16 at 19:59
paparazzopaparazzo
4,6621230
answered Aug 3 '16 at 19:59
paparazzopaparazzo
4,6621230
answered Aug 3 '16 at 19:59
paparazzopaparazzo
4,6621230
answered Aug 3 '16 at 19:59
paparazzopaparazzo
4,6621230
4,6621230
add a comment |
add a comment |
if you group by material, you can select the max sales and group_concat the applicability. this will give one value for the sale of material x with a new column that shows the combined, filtered uses. then a subquery will show the correct sum of sales per material (or order, or however else you decide to group).
I added group by OrderID in the subquery since it seems like you're looking for John's lifetime sales?
select ID, Name, sum(Sales), Material, Applicability
(Select ID, Name, max(Sales) as Sales, Material, group_concat(Applicability separator ', ') as Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY OrderID, ID, Name, Material) sub
GROUP BY ID, Name, Material
answered Aug 3 '16 at 22:40
peachpeach
512
add a comment |
if you group by material, you can select the max sales and group_concat the applicability. this will give one value for the sale of material x with a new column that shows the combined, filtered uses. then a subquery will show the correct sum of sales per material (or order, or however else you decide to group).
I added group by OrderID in the subquery since it seems like you're looking for John's lifetime sales?
select ID, Name, sum(Sales), Material, Applicability
(Select ID, Name, max(Sales) as Sales, Material, group_concat(Applicability separator ', ') as Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY OrderID, ID, Name, Material) sub
GROUP BY ID, Name, Material
answered Aug 3 '16 at 22:40
peachpeach
512
add a comment |
if you group by material, you can select the max sales and group_concat the applicability. this will give one value for the sale of material x with a new column that shows the combined, filtered uses. then a subquery will show the correct sum of sales per material (or order, or however else you decide to group).
I added group by OrderID in the subquery since it seems like you're looking for John's lifetime sales?
select ID, Name, sum(Sales), Material, Applicability
(Select ID, Name, max(Sales) as Sales, Material, group_concat(Applicability separator ', ') as Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY OrderID, ID, Name, Material) sub
GROUP BY ID, Name, Material
answered Aug 3 '16 at 22:40
peachpeach
512
if you group by material, you can select the max sales and group_concat the applicability. this will give one value for the sale of material x with a new column that shows the combined, filtered uses. then a subquery will show the correct sum of sales per material (or order, or however else you decide to group).
I added group by OrderID in the subquery since it seems like you're looking for John's lifetime sales?
select ID, Name, sum(Sales), Material, Applicability
(Select ID, Name, max(Sales) as Sales, Material, group_concat(Applicability separator ', ') as Applicability
FROM Customers a
INNER JOIN Orders b on a.ID = b.CustomerID
INNER JOIN Materials c on b.Material = c.Material
WHERE Name = 'John' and (applicability = 'car' or applicability = 'bike')
GROUP BY OrderID, ID, Name, Material) sub
GROUP BY ID, Name, Material
answered Aug 3 '16 at 22:40
peachpeach
512
answered Aug 3 '16 at 22:40
peachpeach
512
answered Aug 3 '16 at 22:40
peachpeach
512
answered Aug 3 '16 at 22:40
peachpeach
512
512
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f145799%2faggregation-joining-and-filtering-with-many-to-many-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Do you need to return applicability as well as filter by it? By removing it, you can use a SELECT DISTINCT.
– Haris Khan
Aug 3 '16 at 19:34