how to check if a type of row exists in a table

Question

Hi I have an order table and a payment table which are linked by order_num. I would like to get entries from order table who have entries in the payment table of PaymentType 'A' as well as PaymentType 'B' and their amounts match. Example order number 411 should only be returned to me if it has at least two payments and one of them is paymenttype 'A' and the other one is paymenttype 'b' and amount for both is 45

Thanks.

Answer 1

Like the comments state, there is very little info to go off of for a truly definitive answer. Here goes a possible solution, at least in a basic form.

SELECT *
FROM Orders o
LEFT JOIN Payment p1 ON o.order_num = p1.order_num
LEFT JOIN Payment p2 ON o.order_num = p2.order_num
WHERE p1.Type = "A"
AND p2.Type = "B"
AND p1.Amount = p2.Amount

Answer 2

As long as you can guarantee that there are at most one A and one B row:

SELECT
     <columns here>
FROM
     Orders O
INNER JOIN Payments PA ON
     PA.order_number = O.order_number AND
     PA.payment_type = 'A'
INNER JOIN Payments PB ON
     PB.order_number = O.order_number AND
     PB.payment_type = 'B'
WHERE
     PA.amount = PB.amount

Answer 3

How about this:

SELECT      o.OrderID,
            o.Amount
FROM        Order AS o
INNER JOIN  Payment AS pA ON pA.OrderID = o.OrderID AND pA.PaymentType = 'A'
INNER JOIN  Payment AS pB ON pB.OrderID = o.OrderID AND pB.PaymentType = 'B'
WHERE       pA.Amount = pB.Amount
GROUP BY    o.OrderID,
            o.Amount

Answer 4

Making some (hopefully) reasonable assumptions about the names of tables, columns, etc, how about...

SELECT *
    FROM ORDER_TABLE
    WHERE EXISTS (SELECT *
                      FROM PAYMENT_TABLE
                      WHERE PAYMENT_TYPE         = 'A'               AND
                            PAYMENT_TABLE.ORDER  = ORDER_TABLE.ORDER AND
                            PAYMENT_TABLE.AMOUNT = ORDER_TABLE.AMOUNT) AND
          EXISTS (SELECT *
                      FROM PAYMENT_TABLE
                      WHERE PAYMENT_TYPE         = 'B'               AND
                            PAYMENT_TABLE.ORDER  = ORDER_TABLE.ORDER AND
                            PAYMENT_TABLE.AMOUNT = ORDER_TABLE.AMOUNT);

Answer 5

Assumes amounts exist only in the payment table, and that the payment table may have multiple records for a payment type.

select [columns]
  from ORDER O
 where exists (select null from PAYMENT PA, PAYMENT PB
                where PA.PAYMENT_TYPE = 'A'
                  and PB.PAYMENT_TYPE = 'B'
                  and PA.ORDER_NUM    = O.ORDER_NUM
                  and PB.ORDER_NUM    = O.ORDER_NUM
                  and PA.AMOUNT       = PB.AMOUNT)