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