question

sambasivamk avatar image
sambasivamk asked

Need help with SQL Query

I need help with SQL query to combine individual queries I wrote. ALl the individual query corresponds to one table or more table but linked to main table. I have sample in attached sheet. Im looking for help to combine these individual queries into one query and need some help. please help me[link text][1] SELECT * FROM LT/RT/Payf/AT WHERE FK ID = (SELECT Id FROM L WHERE Col5 = '') [1]: /storage/temp/3796-sampl.xlsx
joinssql query
sampl.xlsx (8.8 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image
GPO answered
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 ;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sambasivamk avatar image
sambasivamk answered
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?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO avatar image GPO commented ·
If you paste it into SSMS and run it you'll be able to see what it's doing. You'll be able to select from each of the temp tables individually to see what they contain.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.