As you mentioned in the question, the shared table will have DataAreaId values of the virtual company id rather than an actual company id.
If you need to be somewhat dynamic and span multiple companies and potentially multiple virtual companies, you can make the query more complex and lookup the virtual company id by table collection name.
The virtual company to actual company mapping can be seen on the SQL side in the VIRTUALDATAAREALIST table.
SELECT * FROM VIRTUALDATAAREALIST ORDER BY VIRTUALDATAAREA
The Table Collection associations to Virtual Companies can be found in the TABLECOLLECTIONLIST table.
SELECT * FROM TABLECOLLECTIONLIST
Putting this together with the query in your question could look something like this (untested):
SELECT *
FROM PRODTABLE
LEFT OUTER JOIN INVENTTABLE ON PRODTABLE.ITEMID = INVENTTABLE.ITEMID
AND (
PRODTABLE.DATAAREAID = INVENTTABLE.DATAAREAID
OR
INVENTTABLE.DATAAREAID = (
SELECT VIRTUALDATAAREALIST.VIRTUALDATAAREA
FROM VIRTUALDATAAREALIST
INNER JOIN TABLECOLLECTIONLIST ON VIRTUALDATAAREALIST.VIRTUALDATAAREA = TABLECOLLECTIONLIST.VIRTUALDATAAREA
WHERE ID = PRODTABLE.DATAAREAID
AND TABLECOLLECTIONLIST.TABLECOLLECTION = '<SHARED_TABLE_TABLE_COLLECTION_NAME>'
)
)
This probably isn't the best approach in terms of performance, but it's only meant to provide an example of how the data is handled. In most cases, I would assume this could be simplified to just include the company id(s), both virtual and actual, that you're concerned with.
A more common approach may be to limit the query with a where clause with at least DataAreaId. With that, you could select and store the related InventTable DataAreaId before-hand, then use that parameter in the later select statement to avoid the repeated sub-selects.
Yet another approach could be to avoid using InventTable.DataAreaId altogether, depending upon your data and your system. It is possible to adjust table indexes such that DataAreaId is not the leftmost column if your exact scenario warrants.
