| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-26-11, 10:58
|
|
Registered User
|
|
Join Date: Nov 2011
Posts: 2
|
|
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
|
|
11-27-11, 06:03
|
|
Registered User
|
|
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 620
|
|
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.
|
|
11-27-11, 06:24
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
|
|
just curious ronan, why (do you use those (unnecessary) parentheses)?
|
|
11-27-11, 08:40
|
|
Registered User
|
|
Join Date: Nov 2011
Posts: 2
|
|
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 */;
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Fetching data from Table1 according to Table2
