I have a database of financial data that I am try to
match a holdings file against... The holdings file has
cusips that I look up in a lookup table to identify
what dataset/table they are in.
I need to check that the cusip is in the lookup
table, and that it has the proper type before
I grab any external info on it. The problem is that
I have a highly normalized structure, and am using
left joins so that I can retain the info from the
holdings file and just not get the extended data.
The table structure is a follows...
Holdings
+-lookup
+-lookup info
+-datasets
+-dataset types
I left join the holdings to the lookup file on cusips,
then get the ids to left join into the info table which
links cusips to their datasets. I left join down the line
until I get the type. The problem is... I want everything
beyond the holdings table to fail if the cusip is not present
or if it is present and the type specified in the holdings
file does not match the type at the end of the chain.
Can this be done?
The current query I am working with is below it is trying
to get a summary of all the types with their counts.
But one group of cusips is under treas type and corp
type, and if you ask for treas type it also counts it
under Unknown because of the corp fork...
SELECT IFNULL(T.type,'Unknown') as type,COUNT(DISTINCT H.cusip)
FROM $table AS H
LEFT JOIN pool_lookup.lkup AS L ON H.cusip = L.cusip
LEFT JOIN pool_lookup.lkup_info AS I ON I.lid = L.lid
LEFT JOIN pool.datasets AS D ON D.did = I.did
LEFT JOIN pool.dataset_types AS T ON T.tid = D.tid AND T.type = H.type
GROUP BY T.type ORDER BY T.type";
--
___ __ __ __ _ _ ____ _ _ ____ ____
/ __)( )( ) /__\( \/ )( ___) ( \( )( ___)(_ _)
\__ \ )(__)( /(__)\\ / )__) ) ( )__) )(
(___/(______)(__)(__)\/ (____)()(_)\_)(____) (__)
Brought to you by the anarcho-syndicalist commune.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
