I have been trying to get all column vs. table regardless of alias used, for example, assume this query

SELECT e.name, 
       (SELECT COUNT(*) FROM projects AS p WHERE p.employee_id = e.employee_id) AS project_count,
       (SELECT COUNT(*) FROM tasks AS p WHERE p.employee_id = e.employee_id) AS task_count
FROM employees AS e;

Considering that alias name can be reused across different scopes I can't simply collect details like column, table and alias and do the work at last How I can do that, below is my try If you have any suggestion or any other algorithm please let me know thanks.

In the code below, I’m resolving tables for a given alias at the end of each scope.

ScopedData class is used to store data on different query scopes. When exiting from that scope I remove the data associated with the current scope by calliing pop().

SqlTokenVisitor class extending TablesNamesFinder sligtly differs from it's parent. I've overriden one of it's visit() methods to change the order of visiting.

import java.util.Collections;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.function.Supplier;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.ParenthesedFromItem;
import net.sf.jsqlparser.statement.select.ParenthesedSelect;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SelectVisitor;
import net.sf.jsqlparser.statement.select.WithItem;
import net.sf.jsqlparser.util.TablesNamesFinder;

class ScopedData<T> extends LinkedList<T> {
    private final Supplier<T> creator;

    private ScopedData<T> nonEmptyScope() {
        if (this.isEmpty()) {
            throw new IllegalStateException("Currently scope is empty");
        } else {
            return this;
        }
    }

    public ScopedData(Supplier<T> creator) {
        this.creator = creator;
    }

    public void enterScope() {
        this.push(creator.get());
    }

    public void exitScope() {
        this.nonEmptyScope().pop();
    }

    public T current() {
        return this.nonEmptyScope().peek();
    }
}

class SqlTokenVisitor<T> extends TablesNamesFinder<T> {
    // I have overridden this method because I have changed the order of visiting from in my class
    @Override
    public <S> T visit(PlainSelect plainSelect, S context) {
        List<WithItem> withItemsList = plainSelect.getWithItemsList();
        if (withItemsList != null && !withItemsList.isEmpty()) {
            for (WithItem withItem : withItemsList) {
                withItem.accept((SelectVisitor<?>) this, context);
            }
        }
        if (plainSelect.getFromItem() != null) {
            plainSelect.getFromItem().accept(this, context);
        }
        if (plainSelect.getJoins() != null) {
            visitJoins(plainSelect.getJoins(), context);
        }
        if (plainSelect.getWhere() != null) {
            plainSelect.getWhere().accept(this, context);
        }
        if (plainSelect.getHaving() != null) {
            plainSelect.getHaving().accept(this, context);
        }
        if (plainSelect.getOracleHierarchical() != null) {
            plainSelect.getOracleHierarchical().accept(this, context);
        }
        if (plainSelect.getSelectItems() != null) {
            for (SelectItem<?> item : plainSelect.getSelectItems()) {
                item.accept(this, context);
            }
        }
        return null;
    }

    private <S> T visitJoins(List<Join> joins, S context) {
        for (Join join : joins) {
            join.getFromItem().accept(this, context);
            join.getRightItem().accept(this, context);
            for (Expression expression : join.getOnExpressions()) {
                expression.accept(this, context);
            }
        }
        return null;
    }

    public SqlTokenVisitor() {
        this.init(true);
    }
}

/**
 * Scoped visitor helps to process query in scoped manner for example assume this query,
 * 
 * ```
 * SELECT e.name, // new scope (select 1)
 *      (SELECT COUNT(*) FROM projects AS p WHERE p.employee_id = e.employee_id // new scope (select 2)) AS project_count, 
 *      (SELECT COUNT(*) FROM tasks AS p WHERE p.employee_id = e.employee_id // new scope (select 3)) AS task_count
 * FROM employees AS e;
 * ```
 * 
 * parse_statement(select_1):
 * push_state(stack, select_1)
 *      parse_statement(select_2),
 *      push_state(stack, select_2)
 *      use_state(stack)
 *      pop_state(stack)
 *      parse_statement(select_3)
 *      push_state(stack, select_3)
 *      use_state(stack)
 *      pop_state(stack)
 * use_state(stack)
 * pop_state(stack)
 * 
 * we are doing pop since used data no more needed also it helps to reuse the same variable 
 * in different scope. Another use of stack is assume
 * 
 * ```
 * select a.name, (select a.name from table1 as a) from table2 as a
 * ```
 * 
 * upon pushing state of sub select it hides the outer variable `a` since it is located at
 * bottom and we look up symbol from top of stack which is good since we need to use nearest
 * matching variable.
 * 
 * In below implementation we simply call enterScope before visit and exitScope 
 * after visit Since the parser parses the Elements as tree we can simply
 * use that,
 * 
 * enteringScope()          <- 1
 * select
 * |
 * |-- enteringScope()      <- 2
 * |-- select
 * |-- exitingScope()       <- 2
 * |
 * |-- enteringScope()      <- 3
 * |-- select
 * |-- exitingScope()       <- 3
 * |
 * exitingScope()           <- 1
 */
abstract class ScopedVisitor<T> extends SqlTokenVisitor<T> {
    @Override
    public <S> T visit(PlainSelect select, S context) {
        this.enteringScope();
        super.visit(select, context);
        this.exitingScope();
        return null;
    }

    public abstract void enteringScope();

    public abstract void exitingScope();
}

class ColumnVisitor extends ScopedVisitor<Void> {
    private void processScope(ScopeData scopeData) {
        for (Column column : scopeData.columns) {
            String resolvedTable = this.resolveAlias(column.getTable()).getName();
            System.out.println(column.getColumnName() + " : " + resolvedTable);
        }
    }

    private Table resolveAlias(Table alias) {
        // Try to find alias table in scopes
        for (ScopeData data : scopedData) {
            Table resolved = resolveAlias(data.tables, alias);
            if (resolved != null) {
                return resolved;
            }
        }
        // if no alias found then it is original table
        return alias;
    }
    
    public Table resolveAlias(List<Table> tables, Table alias) {
        // since if one table alias matches with another tabe name we can resolve it as table
        for (Table table : tables) {
            if (table.getAlias() != null && table.getAlias().getName().equals(alias.getName())) {
                return table;
            }
        }
        return null;
    }

    @Override
    public <S> Void visit(LateralSubSelect lateralSubSelect, S context) {
        // we are traking other table that are not actual table but alias
        if (lateralSubSelect.getAlias() != null) {
            this.scopedData.current().otherTables.add(lateralSubSelect.getAlias().getName());
        }
        super.visit(lateralSubSelect, context);
        return null;
    }

    @Override
    public <S> Void visit(WithItem withItem, S context) {
        this.scopedData.current().otherTables.add(withItem.getAlias().getName());
        super.visit(withItem, context);
        return null;
    }

    @Override
    public <S> Void visit(ParenthesedSelect select, S context) {
        if (select.getAlias() != null) {
            this.scopedData.current().otherTables.add(select.getAlias().getName());
        }
        super.visit(select, context);
        return null;
    }

    @Override
    public <S> Void visit(ParenthesedFromItem parenthesis, S context) {
        if (parenthesis.getAlias() != null) {
            this.scopedData.current().otherTables.add(parenthesis.getAlias().getName());
        }
        super.visit(parenthesis, context);
        return null;
    }

    @Override
    public <S> Void visit(Column column, S context) {
        super.visit(column, context);
        if (column.getTable() != null) {
            this.scopedData.current().columns.add(column);
        }

        return null;
    }

    @Override
    public <S> Void visit(Table table, S context) {
        super.visit(table, context);
        this.scopedData.current().tables.add(table);
        return null;
    }

    @Override
    public void enteringScope() {
        this.scopedData.enterScope();
    }

    @Override
    public void exitingScope() {
        this.processScope(this.scopedData.current());
        this.scopedData.exitScope();
    }

    private static class ScopeData {
        public final List<Column> columns = new LinkedList<>();
        public final List<Table> tables = new LinkedList<>();
        public final List<String> otherTables = new LinkedList<>();
    }

    private final ScopedData<ScopeData> scopedData = new ScopedData<>(ScopeData::new);
}

public class Main {
    public static void main(String[] args) throws JSQLParserException {
        String query = "SELECT e.name, \n" + //
                "       (SELECT COUNT(*) FROM projects AS p WHERE p.employee_id = e.employee_id) AS project_count,\n" + //
                "       (SELECT COUNT(*) FROM tasks AS p WHERE p.employee_id = e.employee_id) AS task_count\n" + //
                "FROM employees AS e;";
        Statement stmt = CCJSqlParserUtil.parse(query);
        stmt.accept(new ColumnVisitor());
    }
}

Output

employee_id : projects
employee_id : employees
employee_id : tasks
employee_id : employees
name : employees

You can find JSqlParser in github