magento join sales_flat_quote_payment with sales_flat order tables

Question

I am trying to show last4 of the CC and the Card type in a report, by going through the Database I have learned that inside sales_flat_quote_payment there is a field called additional information, and that field seems to have all the info I need, so I would need to extract if out of there. but the issue is that I am trying to find a way to join the sales_flat_quote_payment with sales_flat order tables so I can match the records by increment_id, but can't seem to find a way to do this.

I already tried:

orders->getSelect()->join(
    array('p' => $orders->getResource()->getTable('sales/order_payment')),
    'p.parent_id = main_table.entity_id',
    array('cc_last4' => 'p.cc_last4',  'cc_type'  => 'p.cc_type')
);

'OrderCreditCardLast4' => $order['cc_last4'],

but with no luck.

Answer 1

assuming you have the order number, do this:

select a.reserved_order_id, b.cc_last4
    from sales_flat_quote a, sales_flat_quote_payment b
    where b.quote_id=a.entity_id and a.reserved_order_id='YOUR ORDER NUMBER HERE';