VBA ADODB: left join

Question

Could you explain me please how to performe the left join in my code ADODB. Actually, I have a inner join in my code, I don't know how to modify the syntax :

Public Sub SUPP_D_ATTENTE(NO_POLICE As String)
Dim RECSET As New ADODB.Recordset
RECSET.Open "select  sousc.lp_etat_doss as cd_rga from db_dossier sousc, db_personne pers, db_garantie gar, db_ctrat_support ctrats, db_param_mandat_op mandaop, db_rga rga" & _
            " where sousc.no_police = '" & NO_POLICE & "' and sousc.cd_dossier = 'SOUSC' and sousc.lp_etat_doss not in ('ANNUL','A30','IMPAY')

Thank you for your help

CDP1802 · Accepted Answer · 2021-02-05 19:01:08Z

1

The JOIN comes after the SELECT and before the WHERE

 " SELECT sousc.lp_etat_doss as cd_rga" & _
 " FROM db_dossier sousc" & _
 
 " LEFT JOIN db_garantie gar" & _
 "   ON gar.is_protocole = sousc.is_protocole" & _
   
 " WHERE sousc.no_police = '" & NO_POLICE & "'" & _
 "   AND sousc.cd_dossier = 'SOUSC'" & _
 "   AND sousc.lp_etat_doss NOT IN ('ANNUL','A30','IMPAY')" & _ 
 "   AND gar.cd_garantie  = 'DEDIE'"

answered Feb 5, 2021 at 19:01

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

VBA_Anne_Marie Over a year ago

Thank you for your answer. The « where » statement cames always at the end ? I mean that firstly, before the joining, I need to filter the data db_dossier.

CDP1802 Over a year ago

@Marie The WHERE filters the data. LEFT JOIN returns all records - see w3schools.com/sql/sql_join.asp. I suspect what you need is a subquery - see learn.microsoft.com/en-us/sql/relational-databases/performance/…

Dy.Lee · Accepted Answer · 2021-02-06 00:28:18Z

0

Assuming you have 3 tables, you need to create a subquery. If you have 4 tables, you will have to create one more subquery.

tableA, tableB, tableC

select c.field1, c.field2, d.field1, d.field2 from
    ( select a.field1 as field1, b.field2 as field2 from
        ( select * from tableA where field1 = 'aaa' and field2 ='123') as a left join tableB as b
        on a.field1= b.field1
     ) as c
left join tableC as d 
on c.field2 = d.field2

edited Feb 6, 2021 at 0:28

answered Feb 6, 2021 at 0:12

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Collectives™ on Stack Overflow

VBA ADODB: left join

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related