Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Question Unanswered: Fetching data from Table1 according to Table2

    Hello,
    Here is my problem,
    I have 3 tables
    -----------
    orderproducts (this table stores products and quantities ordered)
    opid (id)
    orderid (sipariş kimliği)
    productid (ürün kimliği)
    quantity (adet)
    price (fiyat)

    shipments (stores shipments with tracking numbers)
    shipmentid
    orderid
    trackinnumber

    shipmentproducts (this table stores products in shipments)
    spid
    productid
    quantity
    shipmentid
    opid (<---- I ve just add this to make it simpler)
    -----------------------------

    On order, they add orderproducts like product1 X 10 quantity.
    On logistics department, they create a shipment, and sometime ship only 5 products.

    So we want to select orders never-shipped, and partially shipped.

    Basicly
    I want to select from orderproducts where is not fully shipped at shipmentproduct table

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try the following

    SELECT o.orderid, o.productid, o.quantity orderedquantity, SUM(COALESCE(sp.quantity,0)) shippedquantity
    FROM orderproducts o
    LEFT JOIN shipments s ON (o.orderid = s.orderid)
    LEFT JOIN shipmentproducts sp ON (s.shipmentid = sp.shipmentid AND o.productid = sp.productid)
    GROUP BY o.orderid, o.productid, o.quantity;

    This should return to you the orderid, the number of product items ordered and the number of product items shipped.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just curious ronan, why (do you use those (unnecessary) parentheses)?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2011
    Posts
    2

    Thumbs up returns zero

    It returns zero at the last column.. i created DB...
    ps. just sp.quantity is sp.qty in my table.

    Code:
    -- phpMyAdmin SQL Dump
    -- version 3.4.5
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Nov 27, 2011 at 02:36 PM
    -- Server version: 5.5.16
    -- PHP Version: 5.3.8
    
    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    SET time_zone = "+00:00";
    
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    
    --
    -- Database: `cart`
    --
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `orderproducts`
    --
    
    CREATE TABLE IF NOT EXISTS `orderproducts` (
      `opid` int(11) NOT NULL AUTO_INCREMENT,
      `orderid` int(11) NOT NULL,
      `productid` int(11) NOT NULL,
      `name` int(11) NOT NULL,
      `quantity` int(11) NOT NULL,
      `price` int(11) NOT NULL,
      PRIMARY KEY (`opid`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
    
    --
    -- Dumping data for table `orderproducts`
    --
    
    INSERT INTO `orderproducts` (`opid`, `orderid`, `productid`, `name`, `quantity`, `price`) VALUES
    (1, 2, 1, 1, 10, 100),
    (2, 2, 2, 0, 20, 200),
    (3, 3, 3, 0, 1, 111);
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `shipmentproducts`
    --
    
    CREATE TABLE IF NOT EXISTS `shipmentproducts` (
      `spid` int(11) NOT NULL,
      `productid` int(11) NOT NULL,
      `qty` int(11) NOT NULL,
      `shipmentid` int(11) NOT NULL,
      `opid` int(11) NOT NULL,
      PRIMARY KEY (`spid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `shipmentproducts`
    --
    
    INSERT INTO `shipmentproducts` (`spid`, `productid`, `qty`, `shipmentid`, `opid`) VALUES
    (1, 1, 5, 5, 1);
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `shipments`
    --
    
    CREATE TABLE IF NOT EXISTS `shipments` (
      `shipmentid` int(11) NOT NULL AUTO_INCREMENT,
      `trackingnumber` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      `orderid` int(11) NOT NULL,
      PRIMARY KEY (`shipmentid`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
    
    --
    -- Dumping data for table `shipments`
    --
    
    INSERT INTO `shipments` (`shipmentid`, `trackingnumber`, `orderid`) VALUES
    (1, '222555', 2);
    
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •