Multiply joins in vba excel

• ant6729

  SSC Journeyman

  Points: 94

  More actions

  December 24, 2018 at 5:41 am

  #77295

  Hello, everyone I have 4 tables

  1/Cars

  CarId	CarBrandId	RegistrationNumber	MaxWeight
  1	33	12345	1
  2	34	12346	1.1
  3	33	12347	1.2
  4	33	12348	1.3
  5	33	12349	1.4
  6	36	12350	1.5
  7	37	12351	1.6
  8	38	12357	1.7
  9	34	12353	1.8
  10	34	12354	1.9
  11	35	12355	2
  12	33	12356	2.1

  2/CarBrand

  CarBrandId	CarBrandName
  33	Renault
  33	Renault
  34	Volvo
  35	BMW
  36	Niva
  37	HONDA
  38	Lada
  37	HONDA
  37	HONDA
  39	Aston Martin

  3/Drivers

  DriverID	Name1
  45	Ivanov
  46	Petrov
  47	Sidorov
  48	Ivanov1
  49	Petrov1
  50	Ivanov
  51	Ivanov2
  52	Petrov2
  53	Ivanov
  54	Ivanov3
  55	Petrov3
  56	Sidorov3

  4/ Carlist

  CarListid	CarId	DriverID	CarListDate
  101	1	45	21.13.2009 13:13:35
  102	2	46	21.13.2008 13:13:36
  103	3	46	15.13.2008 13:13:34
  104	4	45	16.13.2008 13:13:34
  105	5	45	21.13.2008 13:13:36
  106	6	50	18.13.2008 13:13:35
  107	7	51	21.13.2008 13:13:36
  108	8	52	20.13.2008 13:13:36
  109	9	53	21.13.2008 13:13:36
  110	10	53	22.13.2008 13:13:36
  111	11	55	23.13.2008 13:13:37
  112	12	53	24.13.2008 13:13:37

  I want to get  CarListid of those drivers whos name1 Ivanov and whos car was for instance VOLVO
  So, I have a query, that works
  'strSQL = "SELECT [Drivers$].DriverID, [CarList$].CarId, [Cars$].CarId, [Cars$].CarBrandId " _
'& "FROM ([Drivers$]" _
'& "inner JOIN [CarList$] ON [Drivers$].DriverID = [CarList$].DriverID)" _
'& "inner JOIN [Cars$] ON [CarList$].CarId = [Cars$].CarId " _
'& "Where Name1 like 'Petrov%'"

  and that doesnt

  'strSQL = "SELECT [Drivers$].DriverID, [CarList$].CarId, [Cars$].CarBrandId, [CarBrand$].CarBrandId" _
'& "FROM (([Drivers$]" _
'& " inner JOIN [CarList$] ON [Drivers$].DriverID = [CarList$].DriverID)" _
'& " inner JOIN [Cars$] ON [CarList$].CarId = [Cars$].CarId)" _
'& " inner JOIN [CarsBrand$] ON [Cars$].CarBrandId = [CarsBrand$].CarBrandId " _
'& " Where Name1 like 'Ivanov%'"

  Could anyone help me to get these carlistid,,,,&

  May be my queries wrong,,,I dont exclude syntax and logical nuances...

• Thom A

  SSC Guru

  Points: 99126

  More actions

  December 24, 2018 at 8:18 am

  #2017213

  When you say "it doesn't work", what do you mean? Do you get an error, unexpected results, Excel crashes..? Ideally, if you can, provide your datas DDL and DML statements as well; my signature covers how to do that. What results are you expecting for your queries?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ant6729

  SSC Journeyman

  Points: 94

  More actions

  December 25, 2018 at 6:06 am

  #2017296

  everythig is fine 
  i got the solution)!

• October 27, 2020 at 8:18 pm

  This was removed by the editor as SPAM

• July 12, 2021 at 6:24 pm

  This was removed by the editor as SPAM