What is the best SQL statement to query a table using Excel 2010 VBA with ADO (or vb.net with LINQ)

Table Name: employees

sql table

Right now I am stuck on trying to figure out how to combine two of my SQL queries into one.

Query #1:

    SELECT *
    FROM `employees`
    WHERE `name` = 'BOB'

This obviously just returns 1 row.

Next, I use the group number to look up any other employees belonging to that group.

Query #2:

    SELECT * 
    FROM `employees` 
    WHERE `group` = 1

This is the recordset I want to be able to return by just using one SQL query (instead of two). Using phpMyAdmin and MySQL I figured out a way, but in Excel VBA and ADO I don’t know how to do this same statement.

Combined Queries:

    SET @V1 := (
      SELECT l1.group
      FROM `employees` AS l1
      WHERE l1.name = 'BOB'
    );

    SELECT *
    FROM `employees`
    WHERE `group` = @V1;

Current Situation:

Right now I’m using Excel 2010 VBA and ADO to query a closed workbook that is saved in a local folder.

For this example I grossly oversimplified the structure of the table, but the idea is the same. My actual table in the workbook contains 37,000+ rows with 19 columns.

What is the best way to go about this? I’d like to use LINQ with vb.net if it offers better performance and more flexibility.

Doing two queries using VBA and ADO takes too long, and it feels like Excel sometimes hangs.

Answer

This is the SQL query you need:

SELECT * 
FROM employees 
WHERE group in 
(SELECT group 
FROM employees 
WHERE name like 'BOB')

The part in the parentheses is called a subquery.

Advertisement

Answer