Magento 2 Database – Get Invoice Numbers from Order ID

In this case,you need to create custom mysql code which will create mysql like this

Call your collection:

$collections = Mage::getResourceModel('module/namespace_collection');

Call the db resource singleton model

 $resource = Mage::getSingleton('core/resource');

Carry connection write connection

$writeConnection = $resource->getConnection('core_write');

Make a custom collection by calling write connection and make a custom subquery:

$subSelect =$writeConnection->select()
            ->from(
                array(' table2' => $collections->getTable('module/namespace')),
                array( 'sku', 'MaxDate'=>new Zend_Db_Expr("max(date)"))
            )
        ->group('table2.sku')

Join table

$collections->getSelect()->join(
                array(' tm' => new Zend_Db_Expr(sprintf('(%s)',$subSelect->assemble()))),
                'main_table.sku = tm.sku and main_table.date = tm.MaxDate,
                array()
            );

Select your selective columns

$collections->addFieldToSelect('sku')->addFieldToSelect('date');

Code is not tested and put basic of concept

There are only one relation between Magento quote item tables & sales order tables and that is sales_flat_order.quote_id=sales_flat_quote_item.quote_id.

In magento,sales_flat_quote_item_option does not have any direct relation with sales_flat_order.It have an indirection relation

sales_flat_order.quote_id=sales_flat_quote.quote_id=> sales_flat_quote_item.item_id=sales_flat_quote_item_option.item_id

Query seem like:

SELECT * FROM sales_flat_quote_item_option where item_id in (
        select item_id from  sales_flat_quote_item  where quote_id in (
            SELECT quote_id FROM sales_flat_order  where entity_id =ORDER_ID

        )

)