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;
}







2















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.










share|improve this question














bumped to the homepage by Community 8 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


















2















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.










share|improve this question














bumped to the homepage by Community 8 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














2












2








2








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.










share|improve this question














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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Aug 3 '16 at 19:17









Eric GuanEric Guan

1213




1213





bumped to the homepage by Community 8 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 8 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



















  • 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










2 Answers
2






active

oldest

votes


















0














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





share|improve this answer































    0














    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





    share|improve this answer
























      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
      });


      }
      });














      draft saved

      draft discarded


















      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









      0














      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





      share|improve this answer




























        0














        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





        share|improve this answer


























          0












          0








          0







          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





          share|improve this answer













          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






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Aug 3 '16 at 19:59









          paparazzopaparazzo

          4,6621230




          4,6621230

























              0














              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





              share|improve this answer




























                0














                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





                share|improve this answer


























                  0












                  0








                  0







                  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





                  share|improve this answer













                  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






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Aug 3 '16 at 22:40









                  peachpeach

                  512




                  512






























                      draft saved

                      draft discarded




















































                      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.




                      draft saved


                      draft discarded














                      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





















































                      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







                      Popular posts from this blog

                      Liste der Baudenkmale in Friedland (Mecklenburg)

                      Single-Malt-Whisky

                      Czorneboh