question

This seems to be what you're asking for, but I'm not at all sure it's what you really want. --======= ================================================================================================= --======= Set up some sandbox tables with junk data. IF OBJECT_ID('tempdb..#L') IS NOT NULL DROP TABLE #L; IF OBJECT_ID('tempdb..#LT') IS NOT NULL DROP TABLE #LT; IF OBJECT_ID('tempdb..#RT') IS NOT NULL DROP TABLE #RT; IF OBJECT_ID('tempdb..#PayF') IS NOT NULL DROP TABLE #PayF; IF OBJECT_ID('tempdb..#AT') IS NOT NULL DROP TABLE #AT; --------- ------------------------------------------------------------------------------------------------- SELECT 1 as l_id ,'l_1_col1' as l_col1 ,'l_1_col2' as l_col2 ,'l_1_col3' as l_col3 ,'l_1_col4' as l_col4 ,'l_1_col5' as l_col5 INTO #L UNION ALL SELECT 5 as l_id ,'l_5_col1' as l_col1 ,'l_5_col2' as l_col2 ,'l_5_col3' as l_col3 ,'l_5_col3' as l_col4 ,'' as l_col5 --so we have something to return ; --------- ------------------------------------------------------------------------------------------------- SELECT 1 as lt_id ,1 as l_id ,'lt1_col1' as lt_col1 ,'lt1_col2' as lt_col2 ,'lt1_col3' as lt_col3 INTO #LT UNION ALL SELECT 2 as lt_id ,5 as l_id ,'lt2_col1' as lt_col1 ,'lt2_col2' as lt_col2 ,'lt2_col3' as lt_col3 ; --------- ------------------------------------------------------------------------------------------------- SELECT 1 as rt_id ,1 as l_id ,'rt_1' as rt_col1 ,'rt_1' as rt_col2 ,'rt_1' as rt_col3 ,'rt_1' as rt_col4 INTO #RT UNION ALL SELECT 2 as rt_id ,5 as l_id ,'rt_2' as rt_col1 ,'rt_2' as rt_col2 ,'rt_2' as rt_col3 ,'rt_2' as rt_col4 ; --------- ------------------------------------------------------------------------------------------------- SELECT 1 as payf_id ,1 as l_id ,'payf_1_col1' as payf_col1 ,'payf_1_col2' as payf_col2 ,'payf_1_col3' as payf_col3 ,'payf_1_col4' as payf_col4 ,'payf_1_col5' as payf_col5 ,'payf_1_col6' as payf_col6 ,'payf_1_col7' as payf_col7 INTO #PayF UNION ALL SELECT 2 as payf_id ,5 as l_id ,'payf_2_col1' as payf_col1 ,'payf_2_col2' as payf_col2 ,'payf_2_col3' as payf_col3 ,'payf_2_col4' as payf_col4 ,'payf_2_col5' as payf_col5 ,'payf_2_col6' as payf_col6 ,'payf_2_col7' as payf_col7 ; --------- ------------------------------------------------------------------------------------------------- SELECT 1 as at_id ,1 as l_id ,'at_1_col1' as at_col1 ,'at_1_col2' as at_col2 ,'at_1_col3' as at_col3 ,'at_1_col4' as at_col4 ,'at_1_col5' as at_col5 ,'at_1_col6' as at_col6 ,'at_1_col7' as at_col7 INTO #AT UNION ALL SELECT 2 as at_id ,5 as l_id ,'at_2_col1' as at_col1 ,'at_2_col2' as at_col2 ,'at_2_col3' as at_col3 ,'at_2_col4' as at_col4 ,'at_2_col5' as at_col5 ,'at_2_col6' as at_col6 ,'at_2_col7' as at_col7 ; --------- ------------------------------------------------------------------------------------------------- WITH l as ( SELECT l_id FROM #L WHERE l_col5 = '' ) SELECT * FROM l JOIN #LT lt ON l.l_id = lt.l_id JOIN #RT rt ON l.l_id = rt.l_id JOIN #AT a ON l.l_id = a.l_id JOIN #PayF payf ON l.l_id = payf.l_id ;

Thanks much. One question, will this fine query fetch data from all the table? if so that is what Im looking for. Also what for these lines 1 as l_id etc in each query?