Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Stored Procedure involving multiple tables

Author  Topic  Tekhne Starting Member 3 Posts Posted - 2005-07-05 : 17:02:18 I have several tables that I want to use in stored procedures to output certain information. Table: Lab_Master_ Row_ID | LW_2HrPP | LW_17KETO24HR | LW_FreeAndrogen 1 | 1 | 0 | 1 2 | 0 | 0 | 1 3 | 1 | 1 | 0 Table: Labtrig_test_xref Test_Code_ID | Condition01 2HrPP | Lab_Master_.LW_2HrPP 17Keto24HR | Lab_Master_.LW_17KETO24HR FAI | Lab_Master_.LW_FreeAndrogen Table: Lab_Xref labtest_key | lab_component_key 2HrPP | ALB 2HrPP | ALKP 2HrPP | ALT FAI | ALKP FAI | ALT FAI | CA Basically, Lab_Xref.Labtest_key = Labtrig_test_xref.test_code_id. Labtrig_test_xref contains all of our lab tests. Some lab tests have multiple components in them. These components are listed in Lab_Xref. For every condition01 in labtrig_test_xref, there is a column in Lab_Master_ named the same thing. This is what I need: First, the stored procedure needs to check all of the columns in Lab_Master_ that begin with "LW_" to see which have a value of '1'. Then it needs to take all of those and check which Test_Code_ID's are associated with them in table LabTrig_Test_Xref. Next, it must check to see which test_code_id's are in the Lab_Xref table. If they are not in the table, then simply record the test_code_id. If they are in the table, record every lab_component_key associated with it. Then it needs to check to see if there are any duplications within. It must report which test_code_id's / lab_component_key's are duplicated and the assocated test_code_ID (if its a lab_component_key). Did I make any sense?