Geek asks and answers

Display records from two tables side by side, matching only certain fields

I have two tables table A:

Customer_ID  Product  Date Of Sale  Pay Meth 1  Pay Meth 2  QTY
-----------  -------  ------------  ----------  ----------  ---
        123  AB       1/1/2012             111         222    1
        123  AB       1/1/2012             111         222    1
        456  AC       2/1/2012             333         444    1

and table B:

Customer_ID  Product  Date Of Sale  Pay Meth 1  Pay Meth 2  QTY
-----------  -------  ------------  ----------  ----------  ---
        123  AB       1/1/2012             111         222    2
        456  AB       1/1/2012             124         111    1

I want to map the data so that the record for the client 123in is table Agrouped as:

Customer_ID  Product  Date Of Sale  Pay Meth 1  Pay Meth 2  QTY
-----------  -------  ------------  ----------  ----------  ---
        123  AB       1/1/2012             111         222    2

and to the right of it appears the following entry from table B:

Customer_ID  Product  Date Of Sale  Pay Meth 1  Pay Meth 2  QTY
-----------  -------  ------------  ----------  ----------  ---
        123  AB       1/1/2012             111         222    2

Also (always there is also) we want to show the third record in table Aand to the right of this record the second record in table B(client 456), because they have the same Customer_ID, ProductandDate of Sale

Therefore, it should look something like this:

Customer_ID  Product  Date Of Sale  Pay Meth 1  Pay Meth 2  QTY  Customer_ID  Product  Date Of Sale  Pay Meth 1  Pay Meth 2  QTY
-----------  -------  ------------  ----------  ----------  ---  -----------  -------  ------------  ----------  ----------  ---
        123  AB       1/1/2012             111         222    1          123  AB       1/1/2012             111         222    1
        456  AC       2/1/2012             333         444    1          456  AB       1/1/2012             124         111    1
+5
source share
2 answers

, qty ,

SELECT a.*, b.*
FROM (
    Select customer_id, product, dateofsale, PayMeth1, PayMeth2, SUM(Qty) as Qty
    from TableA
    Group by customer_id, product, dateofsale, PayMeth1, PayMeth2
) a
JOIN (
    Select customer_id, product, dateofsale, PayMeth1, PayMeth2, SUM(Qty) as Qty
    from TableB
    Group by customer_id, product, dateofsale, PayMeth1, PayMeth2
) b 
ON a.customer_id = b.customer_id
+4

, , SQL JOIN: http://www.tizag.com/sqlTutorial/sqljoin.php

, (), , . , customer_id, SQL :

SELECT *
FROM A
JOIN B
ON A.Customer_ID = B.Customer_ID

, , , 2 , , , , ID (456).

0

More articles:


All Articles