Multiply joins in vba excel

  • ant6729

    Valued Member

    Points: 64

    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: 98023

    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.

  • ant6729

    Valued Member

    Points: 64

    everythig is fine 
    i got the solution)!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply