====== Shipping SQL View ======

There may be a better way to write this, or do accomplish this kind of things, but this is at least a start. Feel free to comment about it here: http://www.magentocommerce.com/group/blog/action/viewpost/302/group/207/

Here is my select statement:
<code>
select     so.increment_id as 'order_no',

        /*sales_order_entity_id*/
        /*
        soi_sa.value as 'shipping_address_id', 
        */
        
        (    select xsoev.value 
            from sales_order_entity_varchar xsoev 
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where     xsoev.entity_id = soi_sa.value and 
                    xea.attribute_code = 'firstname'
        ) as 'firstname',
        (    select xsoev.value 
            from sales_order_entity_varchar xsoev 
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where     xsoev.entity_id = soi_sa.value and 
                    xea.attribute_code = 'lastname'
        ) as 'lastname',
        (    select xsoev.value 
            from sales_order_entity_varchar xsoev 
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where     xsoev.entity_id = soi_sa.value and 
                    xea.attribute_code = 'company'
        ) as 'company',
        (    select xsoev.value 
            from sales_order_entity_varchar xsoev 
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where     xsoev.entity_id = soi_sa.value and 
                    xea.attribute_code = 'street'
        ) as 'street',
        (    select xsoev.value 
            from sales_order_entity_varchar xsoev 
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where     xsoev.entity_id = soi_sa.value and 
                    xea.attribute_code = 'region'
        ) as 'region',
        (    select xsoev.value 
            from sales_order_entity_varchar xsoev 
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where     xsoev.entity_id = soi_sa.value and 
                    xea.attribute_code = 'postcode'
        ) as 'postcode',
        (    select xsoev.value 
            from sales_order_entity_varchar xsoev 
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where     xsoev.entity_id = soi_sa.value and 
                    xea.attribute_code = 'country_id'
        ) as 'country_id',
        (    select xsoev.value 
            from sales_order_entity_varchar xsoev 
            inner join eav_attribute xea on xsoev.attribute_id = xea.attribute_id
            where     xsoev.entity_id = soi_sa.value and 
                    xea.attribute_code = 'telephone'
        ) as 'telephone'
from sales_order so
inner join sales_order_int soi_sa on so.entity_id = soi_sa.entity_id
inner join eav_attribute soi_sa_ea on soi_sa.attribute_id = soi_sa_ea.attribute_id and soi_sa_ea.attribute_code = 'shipping_address_id'
/*where so.increment_id = '100000004'*/
</code>

Here is the SQL for creating the view:
<code>

-- View "vi_sales_order_shipping" DDL

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `vi_sales_order_shipping` AS select `so`.`increment_id` AS `order_no`,(select `xsoev`.`value` AS `value` from (`sales_order_entity_varchar` `xsoev` join `eav_attribute` `xea` on((`xsoev`.`attribute_id` = `xea`.`attribute_id`))) where ((`xsoev`.`entity_id` = `soi_sa`.`value`) and (`xea`.`attribute_code` = _utf8'firstname'))) AS `firstname`,(select `xsoev`.`value` AS `value` from (`sales_order_entity_varchar` `xsoev` join `eav_attribute` `xea` on((`xsoev`.`attribute_id` = `xea`.`attribute_id`))) where ((`xsoev`.`entity_id` = `soi_sa`.`value`) and (`xea`.`attribute_code` = _utf8'lastname'))) AS `lastname`,(select `xsoev`.`value` AS `value` from (`sales_order_entity_varchar` `xsoev` join `eav_attribute` `xea` on((`xsoev`.`attribute_id` = `xea`.`attribute_id`))) where ((`xsoev`.`entity_id` = `soi_sa`.`value`) and (`xea`.`attribute_code` = _utf8'company'))) AS `company`,(select `xsoev`.`value` AS `value` from (`sales_order_entity_varchar` `xsoev` join `eav_attribute` `xea` on((`xsoev`.`attribute_id` = `xea`.`attribute_id`))) where ((`xsoev`.`entity_id` = `soi_sa`.`value`) and (`xea`.`attribute_code` = _utf8'street'))) AS `street`,(select `xsoev`.`value` AS `value` from (`sales_order_entity_varchar` `xsoev` join `eav_attribute` `xea` on((`xsoev`.`attribute_id` = `xea`.`attribute_id`))) where ((`xsoev`.`entity_id` = `soi_sa`.`value`) and (`xea`.`attribute_code` = _utf8'region'))) AS `region`,(select `xsoev`.`value` AS `value` from (`sales_order_entity_varchar` `xsoev` join `eav_attribute` `xea` on((`xsoev`.`attribute_id` = `xea`.`attribute_id`))) where ((`xsoev`.`entity_id` = `soi_sa`.`value`) and (`xea`.`attribute_code` = _utf8'postcode'))) AS `postcode`,(select `xsoev`.`value` AS `value` from (`sales_order_entity_varchar` `xsoev` join `eav_attribute` `xea` on((`xsoev`.`attribute_id` = `xea`.`attribute_id`))) where ((`xsoev`.`entity_id` = `soi_sa`.`value`) and (`xea`.`attribute_code` = _utf8'country_id'))) AS `country_id`,(select `xsoev`.`value` AS `value` from (`sales_order_entity_varchar` `xsoev` join `eav_attribute` `xea` on((`xsoev`.`attribute_id` = `xea`.`attribute_id`))) where ((`xsoev`.`entity_id` = `soi_sa`.`value`) and (`xea`.`attribute_code` = _utf8'telephone'))) AS `telephone` from ((`sales_order` `so` join `sales_order_int` `soi_sa` on((`so`.`entity_id` = `soi_sa`.`entity_id`))) join `eav_attribute` `soi_sa_ea` on(((`soi_sa`.`attribute_id` = `soi_sa_ea`.`attribute_id`) and (`soi_sa_ea`.`attribute_code` = _utf8'shipping_address_id'))));
</code>

After you have the view created then you can treat the view similar to how you treat a table.
<code>
select *
from vi_sales_order_shipping
</code>

or if you want to reference a single order

<code>
select *
from vi_sales_order_shipping
where order_no = '100000004'
</code>