title: MySQL- Getting a SUM when a variable inside it doesn't exist
body: <p>first post here!</p>
  
  <p>So as a young Padawan in SQL and Databases in general  I am working on creating a database for a business to manage orders/items/prices etc. I want to create a View that from 3 linked tables {items,prices,discounts}(see tables at the bottom) calculates and show the total price of an item. As a rule, the discount column shouldn't be zero( because we only want in the table <code>discounts</code> entries with actual discount , not 0 ) </p>
  
  <p>From the following entries I want to show <strong>all</strong> of them but my view <strong>only</strong> shows the one's with discounts.</p>
  
  <pre><code>insert into items (`item_id`,`item_name`, `item_quantity`) values
  (102,'item1',20),
  (103,'item2',20),
  (404,'item3',20); # &lt;-- It won't be shown if I do SELECT * FROM view;
  
  insert into discounts (`item_id`,`discount`) values
  (102,50),
  (103,25);
  
  insert into prices (`item_id`,`price`) values
  (102,100),
  (103,100),
  (404,100); 
  </code></pre>
  
  <p>And here is my View:</p>
  
  <pre><code>CREATE VIEW ItemsPrice AS
  SELECT 
      i.item_id,
      i.item_name,
      SUM((1-d.discount/100)*p.price*i.item_quantity)   
  
  FROM
      items AS i
      INNER JOIN 
          prices AS p ON i.item_id=p.item_id
      INNER JOIN
          discounts AS d ON (p.item_id=d.item_id)    
  GROUP BY item_id
  ORDER BY total;
  </code></pre>
  
  <p>Here are my tables (just in case I made them wrong):</p>
  
  <pre><code>DROP TABLE IF EXISTS `items`;
  CREATE TABLE `items` (
      `item_id` int(30) NOT NULL,
      `item_name` varchar(35) NOT NULL,
      `item_quantity` double(25,0) ,
      PRIMARY KEY (`item_id`)
  
  );
  #2=======
  DROP TABLE IF EXISTS `prices`;
  CREATE TABLE `prices` (
      `item_id`int(30) NOT NULL,
      `price` decimal(30,2) NOT NULL,
  
      PRIMARY KEY (`item_id`),
       CONSTRAINT `prices_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`)   
  );
  #3=======
  DROP TABLE IF EXISTS `discounts`;
  CREATE TABLE `discounts` (
      `item_id` int(30) NOT NULL,
      `discount` int(3) NOT NULL, 
      PRIMARY KEY (`item_id`),
      CONSTRAINT `discount_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`)
  );
  </code></pre>
  
  <h1>Sorry for not providing a schema. Don't know how to make one.</h1>
  
  <p>Hope I didn't waste much of your time! You are my hero.</p>