To query just ID from a table to multiple table with query containing left outer join, subquery, Max, Inner Join

I have a list of buyerID i need to input select query into the BOLD query below :
and a.user_id in (331, 110, 200, 006)

I'm using Hadoop but query should be almost similarly common.

I'm lost on the code below, anyone can kindly explain and guide me.

Table of buyerID is call <tbl_BID>
Contains only one String field
e.g. of data 331, 09001, 0318095, etc.
it has 80K records.

Thanks alot for any help.

select distinct
a.user_id EID
,a.other_name otherName1
,c.COUNTRY1 Citizenship
,c1.COUNTRY2 rCountry
,c2.COUNTRY2 import_country
,D1.DESC1NAME Description_1
,D2.DESC2NAME Description_2
,D3.DESC3NAME Description_3
,e.C_TYPE C_TYPE
,e.CAT1 CAT1
,e.C_TITLE as C_TITLE
,e.SINCE_DATE as Occupation_SINCE_Date
,e.TO_DATE as Occupation_TO_Date
,List.LName list_reference
,a.is_active inactive1
,a.DEC DEC
,a.last_updated_date rDate
,f.name rel
,pu.EID r_EID
,pu.other_name name1
,pu.COUNTRY1 Citizenship1
,pu.rCountry Residence1
,pu.import_country r_import_country
,pu.DESC1NAME RD_1
,pu.DESC2NAME RD_2
,pu.DESC3NAME RD_3
,pu.C_TYPE C_TYPE
,pu.CAT1 CAT1
,pu.C_TITLE as C_TITLE
,pu.SINCE_DATE as Occupation_SINCE_Date
,pu.TO_DATE as Occupation_TO_Date
,RList.r_ref r_ref
,pu.is_active inactive1
,pu.DEC dec1
,pu.last_updated_date RAC_last_review_date
from
tbl1.tbData a
left outer join
tbl1.tbCCD c
on a.row_id = c.row_id and c.country_type ='Citizen' and a.bDate = c.bDate
left outer join
tbl1.tbCD C1
on a.row_id = c1.row_id and c1.country_type ='Res' and a.bDate = c1.bDate
left outer join
tbl1.tbCD C2
on a.row_id = c2.row_id and c2.country_type ='JC' and a.bDate = c2.bDate
Inner join
tbl1.tbDD D1
on a.row_id = d1.row_id and a.bDate = D1.bDate
left outer join
tbl1.tbDD2 D2
on a.row_id = d2.row_id and a.bDate = D2.bDate
left outer join
tbl1.tbDD3 D3
on a.row_id = d3.row_id and a.bDate = D3.bDate
left outer join
tbl1.tbRD e
on a.row_id = e.row_id and a.bDate = e.bDate
left outer join
tbl1.tbl_AD fa
on a.user_id = fa.user_id and a.row_id = fa.row_id
left outer join
tbl1.tbl_LR f
on fa.code = f.code
left outer join
tbl1.tbLR r
on a.row_id = r.row_id
left outer join
(select ld.id
, ld.LName
from tbl1.LD ld
where ld.bDate ='9999-12-31'
and trim(ld.LName)
not in ('TS - SG',
'TS - HK',
'TS - TH',
'TS - JP',
'TS - KR',
'NA Local')) List
on r.tbl_LDI = List.id
left outer join
(select distinct
a.user_id EID
,a.other_name
,c.COUNTRY1
,c1.COUNTRY2 rCountry
,c2.COUNTRY2 import_country
,D1.DESC1NAME
,D2.DESC2NAME
,D3.DESC3NAME
,e.C_TYPE C_TYPE
,e.CAT1
,e.C_TITLE
,e.SINCE_DATE
,e.TO_DATE
,a.is_active
,a.DEC
,a.last_updated_date
,r.tbl_LDI LDI
from
tbl1.tbData a
left outer join
tbl1.tbCCD c
on a.row_id = c.row_id and c.country_type ='Citizenship' and a.bDate = c.bDate
left outer join
tbl1.tbCD C1
on a.row_id = c1.row_id and c1.country_type ='Residence' and a.bDate = c1.bDate
left outer join
tbl1.tbCD C2
on a.row_id = c2.row_id and c2.country_type ='JC' and a.bDate = c2.bDate
left outer join
tbl1.tbDD D1
on a.row_id = d1.row_id and a.bDate = D1.bDate
left outer join
tbl1.tbDD2 D2
on a.row_id = d2.row_id and a.bDate = D2.bDate
left outer join
tbl1.tbDD3 D3
on a.row_id = d3.row_id and a.bDate = D3.bDate
left outer join
tbl1.tbRD e
on a.row_id = e.row_id and a.bDate = e.bDate
left outer join
tbl1.tbLR r
on a.row_id = r.row_id
where 1=1
and a.row_id in (select MAX(x.row_id) row_id
from tbl1.tbData x
where x.user_id=a.user_id)) pu
on fa.AID = pu.EID
left outer join
(select ld.id
, ld.LName r_ref
from tbl1.LD ld
where ld.bDate ='9999-12-31'
and trim(ld.LName)
not in ('TS - SG',
'TS - HK',
'TS - TH',
'TS - JP',
'TS - KR',
'NA Local')) RList
on pu.LDI = RList.id
where 1=1
and a.status =''
and a.user_id in (331, 110, 200, 006)
and a.row_id in (select MAX(x.row_id) row_id
from tbl1.tbData x
where x.user_id=a.user_id)
order by 1;

hi

i have formatted the code so that it is easy to understand !!!

please click arrow to the left for FORMATTED Sql
SELECT DISTINCT a.user_id            EID, 
                a.other_name         otherName1, 
                c.country1           Citizenship, 
                c1.country2          rCountry, 
                c2.country2          import_country, 
                D1.desc1name         Description_1, 
                D2.desc2name         Description_2, 
                D3.desc3name         Description_3, 
                e.c_type             C_TYPE, 
                e.cat1               CAT1, 
                e.c_title            AS C_TITLE, 
                e.since_date         AS Occupation_SINCE_Date, 
                e.to_date            AS Occupation_TO_Date, 
                List.lname           list_reference, 
                a.is_active          inactive1, 
                a.dec                DEC, 
                a.last_updated_date  rDate, 
                f.NAME               rel, 
                pu.eid               r_EID, 
                pu.other_name        name1, 
                pu.country1          Citizenship1, 
                pu.rcountry          Residence1, 
                pu.import_country    r_import_country, 
                pu.desc1name         RD_1, 
                pu.desc2name         RD_2, 
                pu.desc3name         RD_3, 
                pu.c_type            C_TYPE, 
                pu.cat1              CAT1, 
                pu.c_title           AS C_TITLE, 
                pu.since_date        AS Occupation_SINCE_Date, 
                pu.to_date           AS Occupation_TO_Date, 
                RList.r_ref          r_ref, 
                pu.is_active         inactive1, 
                pu.dec               dec1, 
                pu.last_updated_date RAC_last_review_date 
FROM   tbl1.tbdata a 
       LEFT OUTER JOIN tbl1.tbccd c 
                    ON a.row_id = c.row_id 
                       AND c.country_type = 'Citizen' 
                       AND a.bdate = c.bdate 
       LEFT OUTER JOIN tbl1.tbcd C1 
                    ON a.row_id = c1.row_id 
                       AND c1.country_type = 'Res' 
                       AND a.bdate = c1.bdate 
       LEFT OUTER JOIN tbl1.tbcd C2 
                    ON a.row_id = c2.row_id 
                       AND c2.country_type = 'JC' 
                       AND a.bdate = c2.bdate 
       INNER JOIN tbl1.tbdd D1 
               ON a.row_id = d1.row_id 
                  AND a.bdate = D1.bdate 
       LEFT OUTER JOIN tbl1.tbdd2 D2 
                    ON a.row_id = d2.row_id 
                       AND a.bdate = D2.bdate 
       LEFT OUTER JOIN tbl1.tbdd3 D3 
                    ON a.row_id = d3.row_id 
                       AND a.bdate = D3.bdate 
       LEFT OUTER JOIN tbl1.tbrd e 
                    ON a.row_id = e.row_id 
                       AND a.bdate = e.bdate 
       LEFT OUTER JOIN tbl1.tbl_ad fa 
                    ON a.user_id = fa.user_id 
                       AND a.row_id = fa.row_id 
       LEFT OUTER JOIN tbl1.tbl_lr f 
                    ON fa.code = f.code 
       LEFT OUTER JOIN tbl1.tblr r 
                    ON a.row_id = r.row_id 
       LEFT OUTER JOIN (SELECT ld.id, 
                               ld.lname 
                        FROM   tbl1.ld ld 
                        WHERE  ld.bdate = '9999-12-31' 
                               AND Trim(ld.lname) NOT IN ( 
                                   'TS - SG', 'TS - HK', 'TS - TH', 'TS - JP', 
                                   'TS - KR', 'NA Local' )) List 
                    ON r.tbl_ldi = List.id 
       LEFT OUTER JOIN (SELECT DISTINCT a.user_id   EID, 
                                        a.other_name, 
                                        c.country1, 
                                        c1.country2 rCountry, 
                                        c2.country2 import_country, 
                                        D1.desc1name, 
                                        D2.desc2name, 
                                        D3.desc3name, 
                                        e.c_type    C_TYPE, 
                                        e.cat1, 
                                        e.c_title, 
                                        e.since_date, 
                                        e.to_date, 
                                        a.is_active, 
                                        a.dec, 
                                        a.last_updated_date, 
                                        r.tbl_ldi   LDI 
                        FROM   tbl1.tbdata a 
                               LEFT OUTER JOIN tbl1.tbccd c 
                                            ON a.row_id = c.row_id 
                                               AND 
                               c.country_type = 'Citizenship' 
                                               AND a.bdate = c.bdate 
                               LEFT OUTER JOIN tbl1.tbcd C1 
                                            ON a.row_id = c1.row_id 
                                               AND c1.country_type = 'Residence' 
                                               AND a.bdate = c1.bdate 
                               LEFT OUTER JOIN tbl1.tbcd C2 
                                            ON a.row_id = c2.row_id 
                                               AND c2.country_type = 'JC' 
                                               AND a.bdate = c2.bdate 
                               LEFT OUTER JOIN tbl1.tbdd D1 
                                            ON a.row_id = d1.row_id 
                                               AND a.bdate = D1.bdate 
                               LEFT OUTER JOIN tbl1.tbdd2 D2 
                                            ON a.row_id = d2.row_id 
                                               AND a.bdate = D2.bdate 
                               LEFT OUTER JOIN tbl1.tbdd3 D3 
                                            ON a.row_id = d3.row_id 
                                               AND a.bdate = D3.bdate 
                               LEFT OUTER JOIN tbl1.tbrd e 
                                            ON a.row_id = e.row_id 
                                               AND a.bdate = e.bdate 
                               LEFT OUTER JOIN tbl1.tblr r 
                                            ON a.row_id = r.row_id 
                        WHERE  1 = 1 
                               AND a.row_id IN (SELECT Max(x.row_id) row_id 
                                                FROM   tbl1.tbdata x 
                                                WHERE  x.user_id = a.user_id)) 
                       pu 
                    ON fa.aid = pu.eid 
       LEFT OUTER JOIN (SELECT ld.id, 
                               ld.lname r_ref 
                        FROM   tbl1.ld ld 
                        WHERE  ld.bdate = '9999-12-31' 
                               AND Trim(ld.lname) NOT IN ( 
                                   'TS - SG', 'TS - HK', 'TS - TH', 'TS - JP', 
                                   'TS - KR', 'NA Local' )) RList 
                    ON pu.ldi = RList.id 
WHERE  1 = 1 
       AND a.status = '' 
       AND a.user_id IN ( 331, 110, 200, 006 ) 
       AND a.row_id IN (SELECT Max(x.row_id) row_id 
                        FROM   tbl1.tbdata x 
                        WHERE  x.user_id = a.user_id) 
ORDER  BY 1;

there are several online websites which do SQL Formatting for free
here is one of them !!

http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz

2 Likes

there are .. methods to make complex SQL ... simple to understand !!

please google ..
or
if you are interested ..i could show you

2 Likes

thanks for reply. I'm beginner not sure how to debug and break down the codes. How do i put the table <tbl_BID> into the line with "a.user_id in (331, 110, 200, 006)"

the code was sample from other page but i do not know how to use it as I don't understand.

left outer join tbl_BID tbd a.row_id = tbd.row_id

i added to code .. i have put comments where i added .. please try code !!

SELECT DISTINCT a.user_id            EID, 
                a.other_name         otherName1, 
                c.country1           Citizenship, 
                c1.country2          rCountry, 
                c2.country2          import_country, 
                D1.desc1name         Description_1, 
                D2.desc2name         Description_2, 
                D3.desc3name         Description_3, 
                e.c_type             C_TYPE, 
                e.cat1               CAT1, 
                e.c_title            AS C_TITLE, 
                e.since_date         AS Occupation_SINCE_Date, 
                e.to_date            AS Occupation_TO_Date, 
                List.lname           list_reference, 
                a.is_active          inactive1, 
                a.dec                DEC, 
                a.last_updated_date  rDate, 
                f.NAME               rel, 
                pu.eid               r_EID, 
                pu.other_name        name1, 
                pu.country1          Citizenship1, 
                pu.rcountry          Residence1, 
                pu.import_country    r_import_country, 
                pu.desc1name         RD_1, 
                pu.desc2name         RD_2, 
                pu.desc3name         RD_3, 
                pu.c_type            C_TYPE, 
                pu.cat1              CAT1, 
                pu.c_title           AS C_TITLE, 
                pu.since_date        AS Occupation_SINCE_Date, 
                pu.to_date           AS Occupation_TO_Date, 
                RList.r_ref          r_ref, 
                pu.is_active         inactive1, 
                pu.dec               dec1, 
                pu.last_updated_date RAC_last_review_date 
FROM   tbl1.tbdata a 
       LEFT OUTER JOIN tbl1.tbccd c 
                    ON a.row_id = c.row_id 
                       AND c.country_type = 'Citizen' 
                       AND a.bdate = c.bdate 
       LEFT OUTER JOIN tbl1.tbcd C1 
                    ON a.row_id = c1.row_id 
                       AND c1.country_type = 'Res' 
                       AND a.bdate = c1.bdate 
       LEFT OUTER JOIN tbl1.tbcd C2 
                    ON a.row_id = c2.row_id 
                       AND c2.country_type = 'JC' 
                       AND a.bdate = c2.bdate 
       INNER JOIN tbl1.tbdd D1 
               ON a.row_id = d1.row_id 
                  AND a.bdate = D1.bdate 
       LEFT OUTER JOIN tbl1.tbdd2 D2 
                    ON a.row_id = d2.row_id 
                       AND a.bdate = D2.bdate 
       LEFT OUTER JOIN tbl1.tbdd3 D3 
                    ON a.row_id = d3.row_id 
                       AND a.bdate = D3.bdate 
       LEFT OUTER JOIN tbl1.tbrd e 
                    ON a.row_id = e.row_id 
                       AND a.bdate = e.bdate 
       LEFT OUTER JOIN tbl1.tbl_ad fa 
                    ON a.user_id = fa.user_id 
                       AND a.row_id = fa.row_id 
       LEFT OUTER JOIN tbl1.tbl_lr f 
                    ON fa.code = f.code 
       LEFT OUTER JOIN tbl1.tblr r 
                    ON a.row_id = r.row_id 
       LEFT OUTER JOIN (SELECT ld.id, 
                               ld.lname 
                        FROM   tbl1.ld ld 
                        WHERE  ld.bdate = '9999-12-31' 
                               AND Trim(ld.lname) NOT IN ( 
                                   'TS - SG', 'TS - HK', 'TS - TH', 'TS - JP', 
                                   'TS - KR', 'NA Local' )) List 
                    ON r.tbl_ldi = List.id 
       LEFT OUTER JOIN (SELECT DISTINCT a.user_id   EID, 
                                        a.other_name, 
                                        c.country1, 
                                        c1.country2 rCountry, 
                                        c2.country2 import_country, 
                                        D1.desc1name, 
                                        D2.desc2name, 
                                        D3.desc3name, 
                                        e.c_type    C_TYPE, 
                                        e.cat1, 
                                        e.c_title, 
                                        e.since_date, 
                                        e.to_date, 
                                        a.is_active, 
                                        a.dec, 
                                        a.last_updated_date, 
                                        r.tbl_ldi   LDI 
                        FROM   tbl1.tbdata a 
                               LEFT OUTER JOIN tbl1.tbccd c 
                                            ON a.row_id = c.row_id 
                                               AND 
                               c.country_type = 'Citizenship' 
                                               AND a.bdate = c.bdate 
                               LEFT OUTER JOIN tbl1.tbcd C1 
                                            ON a.row_id = c1.row_id 
                                               AND c1.country_type = 'Residence' 
                                               AND a.bdate = c1.bdate 
                               LEFT OUTER JOIN tbl1.tbcd C2 
                                            ON a.row_id = c2.row_id 
                                               AND c2.country_type = 'JC' 
                                               AND a.bdate = c2.bdate 
                               LEFT OUTER JOIN tbl1.tbdd D1 
                                            ON a.row_id = d1.row_id 
                                               AND a.bdate = D1.bdate 
                               LEFT OUTER JOIN tbl1.tbdd2 D2 
                                            ON a.row_id = d2.row_id 
                                               AND a.bdate = D2.bdate 
                               LEFT OUTER JOIN tbl1.tbdd3 D3 
                                            ON a.row_id = d3.row_id 
                                               AND a.bdate = D3.bdate 
                               LEFT OUTER JOIN tbl1.tbrd e 
                                            ON a.row_id = e.row_id 
                                               AND a.bdate = e.bdate 
                               LEFT OUTER JOIN tbl1.tblr r 
                                            ON a.row_id = r.row_id 
                        WHERE  1 = 1 
                               AND a.row_id IN (SELECT Max(x.row_id) row_id 
                                                FROM   tbl1.tbdata x 
                                                WHERE  x.user_id = a.user_id)) 
                       pu 
                    ON fa.aid = pu.eid 
       LEFT OUTER JOIN (SELECT ld.id, 
                               ld.lname r_ref 
                        FROM   tbl1.ld ld 
                        WHERE  ld.bdate = '9999-12-31' 
                               AND Trim(ld.lname) NOT IN ( 
                                   'TS - SG', 'TS - HK', 'TS - TH', 'TS - JP', 
                                   'TS - KR', 'NA Local' )) RList 
                    ON pu.ldi = RList.id
       LEFT OUTER JOIN  tbl_BID tbd  --- what i added 
				ON a.row_id = tbd.row_id -- what i added 					
WHERE  1 = 1 
       AND a.status = '' 
       AND a.user_id IN ( 331, 110, 200, 006 ) 	   
       AND a.row_id IN (SELECT Max(x.row_id) row_id 
                        FROM   tbl1.tbdata x 
                        WHERE  x.user_id = a.user_id) 
ORDER  BY 1;
1 Like

Encounter multiple subquery not allowed:

if i need to revised a.user_id in (select userid from tbl_BID) 
to replace this line
AND a.user_id IN ( 331, 110, 200, 006 ) 

any idea?

hi i amended refer to "-- changed "
error is Hive: SemanticException [Error 10002]: Line 100:21 Invalid column reference 'mm_id'

could this be group by issue. If yes, "Distinct" don't accept group by.

                    ON pu.ldi = RList.id
       LEFT OUTER JOIN  aa.tbl_BID tbd
                ON a.user_id = tbd.mm_id           -- changed      
WHERE  1 = 1 
       AND a.status = '' 
       AND a.user_id IN (select mm_id from aa.tbl_BID) -- changed

Does this table aa.tbl_BID have that column mm_id

hi yes,
aa.tbl_BID have only one column --> mm_id

hi

is it possible for me to remote desktop to your machine !!

we can fix this quickly instead of endless BACK and Forth

1 Like

hi i can't as is data sensitive although I would like to. the code above is also just sample =)

i hope it is not much of difference as this is in Hive not SQL server which i have used for school last time.

so how can we find a solution !!!

hi and all Gurus, do keep your answers coming. Hope to resolve soon. Once again, thanks.

Also how to do debugging..any good guidance i can follow to accelerate my learning curve in building this kind of multiple joins and sub-query.

hi

when you say accelerate learning curve !!

take notes !!! what kind of notes to take .. ..

how to do debugging ..

-- there is debugger in SSMS for stored procs etc

just a humble update, my peer found my issue. Found out the column was created with exactly '.mm_id' in Hive due to table created via txt file import. that was new..hive can accept special character column field. Drop the table and re-create the field properly to exactly mm_id

removed this as this is not a meaningful line needed

       AND a.user_id IN (select mm_id from aa.tbl_BID) -- changed

Amended to:

                    ON pu.ldi = RList.id
       Inner JOIN  aa.tbl_BID tbd
                ON a.user_id = tbd.mm_id           -- changed      
WHERE  1 = 1 
       AND a.status = '' 

@melon which is why this question was asked. glad you resolved it.

this has happened to me a million times !!!

issue comes .. could be a hundred different things

have to get it resolved ... lot of times i have no clue ..
co worker with domain knowledge .. solves it ..

:wink: :wink: :wink:

yea, but my peer is always busy as he has more responsibility. I try not to disturb him too much if possible.

Can i ask how to optimize the query. I just feel dizzy running the query, it takes decades to get my report. I'm still running the query :X when i'm suppose to finish by today late.
I got 70K records on table <tbl_BID> and it went timeout after 2 hours very sad.

   INNER JOIN  tbl_BID tbd  --- changed to Inner Join 
			ON a.user_id = tbd.userid		-- updated	
SELECT DISTINCT a.user_id            EID, 
                a.other_name         otherName1, 
                c.country1           Citizenship, 
                c1.country2          rCountry, 
                c2.country2          import_country, 
                D1.desc1name         Description_1, 
                D2.desc2name         Description_2, 
                D3.desc3name         Description_3, 
                e.c_type             C_TYPE, 
                e.cat1               CAT1, 
                e.c_title            AS C_TITLE, 
                e.since_date         AS Occupation_SINCE_Date, 
                e.to_date            AS Occupation_TO_Date, 
                List.lname           list_reference, 
                a.is_active          inactive1, 
                a.dec                DEC, 
                a.last_updated_date  rDate, 
                f.NAME               rel, 
                pu.eid               r_EID, 
                pu.other_name        name1, 
                pu.country1          Citizenship1, 
                pu.rcountry          Residence1, 
                pu.import_country    r_import_country, 
                pu.desc1name         RD_1, 
                pu.desc2name         RD_2, 
                pu.desc3name         RD_3, 
                pu.c_type            C_TYPE, 
                pu.cat1              CAT1, 
                pu.c_title           AS C_TITLE, 
                pu.since_date        AS Occupation_SINCE_Date, 
                pu.to_date           AS Occupation_TO_Date, 
                RList.r_ref          r_ref, 
                pu.is_active         inactive1, 
                pu.dec               dec1, 
                pu.last_updated_date RAC_last_review_date 
FROM   tbl1.tbdata a 
       LEFT OUTER JOIN tbl1.tbccd c 
                    ON a.row_id = c.row_id 
                       AND c.country_type = 'Citizen' 
                       AND a.bdate = c.bdate 
       LEFT OUTER JOIN tbl1.tbcd C1 
                    ON a.row_id = c1.row_id 
                       AND c1.country_type = 'Res' 
                       AND a.bdate = c1.bdate 
       LEFT OUTER JOIN tbl1.tbcd C2 
                    ON a.row_id = c2.row_id 
                       AND c2.country_type = 'JC' 
                       AND a.bdate = c2.bdate 
       INNER JOIN tbl1.tbdd D1 
               ON a.row_id = d1.row_id 
                  AND a.bdate = D1.bdate 
       LEFT OUTER JOIN tbl1.tbdd2 D2 
                    ON a.row_id = d2.row_id 
                       AND a.bdate = D2.bdate 
       LEFT OUTER JOIN tbl1.tbdd3 D3 
                    ON a.row_id = d3.row_id 
                       AND a.bdate = D3.bdate 
       LEFT OUTER JOIN tbl1.tbrd e 
                    ON a.row_id = e.row_id 
                       AND a.bdate = e.bdate 
       LEFT OUTER JOIN tbl1.tbl_ad fa 
                    ON a.user_id = fa.user_id 
                       AND a.row_id = fa.row_id 
       LEFT OUTER JOIN tbl1.tbl_lr f 
                    ON fa.code = f.code 
       LEFT OUTER JOIN tbl1.tblr r 
                    ON a.row_id = r.row_id 
       LEFT OUTER JOIN (SELECT ld.id, 
                               ld.lname 
                        FROM   tbl1.ld ld 
                        WHERE  ld.bdate = '9999-12-31' 
                               AND Trim(ld.lname) NOT IN ( 
                                   'TS - SG', 'TS - HK', 'TS - TH', 'TS - JP', 
                                   'TS - KR', 'NA Local' )) List 
                    ON r.tbl_ldi = List.id 
       LEFT OUTER JOIN (SELECT DISTINCT a.user_id   EID, 
                                        a.other_name, 
                                        c.country1, 
                                        c1.country2 rCountry, 
                                        c2.country2 import_country, 
                                        D1.desc1name, 
                                        D2.desc2name, 
                                        D3.desc3name, 
                                        e.c_type    C_TYPE, 
                                        e.cat1, 
                                        e.c_title, 
                                        e.since_date, 
                                        e.to_date, 
                                        a.is_active, 
                                        a.dec, 
                                        a.last_updated_date, 
                                        r.tbl_ldi   LDI 
                        FROM   tbl1.tbdata a 
                               LEFT OUTER JOIN tbl1.tbccd c 
                                            ON a.row_id = c.row_id 
                                               AND 
                               c.country_type = 'Citizenship' 
                                               AND a.bdate = c.bdate 
                               LEFT OUTER JOIN tbl1.tbcd C1 
                                            ON a.row_id = c1.row_id 
                                               AND c1.country_type = 'Residence' 
                                               AND a.bdate = c1.bdate 
                               LEFT OUTER JOIN tbl1.tbcd C2 
                                            ON a.row_id = c2.row_id 
                                               AND c2.country_type = 'JC' 
                                               AND a.bdate = c2.bdate 
                               LEFT OUTER JOIN tbl1.tbdd D1 
                                            ON a.row_id = d1.row_id 
                                               AND a.bdate = D1.bdate 
                               LEFT OUTER JOIN tbl1.tbdd2 D2 
                                            ON a.row_id = d2.row_id 
                                               AND a.bdate = D2.bdate 
                               LEFT OUTER JOIN tbl1.tbdd3 D3 
                                            ON a.row_id = d3.row_id 
                                               AND a.bdate = D3.bdate 
                               LEFT OUTER JOIN tbl1.tbrd e 
                                            ON a.row_id = e.row_id 
                                               AND a.bdate = e.bdate 
                               LEFT OUTER JOIN tbl1.tblr r 
                                            ON a.row_id = r.row_id 
                        WHERE  1 = 1 
                               AND a.row_id IN (SELECT Max(x.row_id) row_id 
                                                FROM   tbl1.tbdata x 
                                                WHERE  x.user_id = a.user_id)) 
                       pu 
                    ON fa.aid = pu.eid 
       LEFT OUTER JOIN (SELECT ld.id, 
                               ld.lname r_ref 
                        FROM   tbl1.ld ld 
                        WHERE  ld.bdate = '9999-12-31' 
                               AND Trim(ld.lname) NOT IN ( 
                                   'TS - SG', 'TS - HK', 'TS - TH', 'TS - JP', 
                                   'TS - KR', 'NA Local' )) RList 
                    ON pu.ldi = RList.id
       INNER JOIN  tbl_BID tbd  --- changed to Inner Join 
				ON a.user_id = tbd.userid		-- updated		
WHERE  1 = 1 
       AND a.status = ''    
       AND a.row_id IN (SELECT Max(x.row_id) row_id 
                        FROM   tbl1.tbdata x 
                        WHERE  x.user_id = a.user_id) 
ORDER  BY 1;

to optimize the query !!

you will have to find out ... why its taking so long !!!

One thing you can try is ... comment one join at a time

the join which you comment after which it runs very quickly is the problem join ..

Now after you identify the join ... see the tables involved and the columns involved in the join
and take it from there !!

one thing is you can add indexes on the columns in the tables on the columns ..

Another way to solve a huge number of joins .. is doing it one join at a time
Lets say
select a join b join c join d join e join f

select a join b first into temp table .. abc
then join abc to c into temp table ... def
then join temp table def to d into temp table

Here another idea is
select a join b join c join d join e
the c join d is the one if you comment it runs fast ..
then you can do it seperately and join to all the others

hope you understand all this !!!
if not .. we can connect via ANYDESK and I can show you

OR

you can google .. Lots of videos with examples there

hi just check if you have done any temp table to reach an optimize query.

Some querying on the table suggest that the business date has many varied values and not necessary '9999-12-31' and I'm trying to get all records for the "userid" in table <tbl_BID>