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

~~~sql
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.

What I am currently doing is resolving tables for an given alias at end of each scope below is the code,

~~~java
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> implements Iterable<T> {
    protected final LinkedList<T> scopes = new LinkedList<>();
    private final Supplier<T> creator;

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

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

    public void exitScope() {
        if (scopes.isEmpty()) {
            throw new IllegalStateException("No scope to exit");
        } else {
            scopes.pop();
        }
    }

    public List<T> scopes() {
        return Collections.unmodifiableList(scopes);
    }

    public T current() {
        if (scopes.isEmpty()) {
            throw new IllegalStateException("No scope to get");
        } else {
            return this.scopes.peek();
        }
    }

    @Override
    public Iterator<T> iterator() {
        return this.scopes.descendingIterator();
    }
}

// I am using different class than TablesNamesFinder but it is almost same as that
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);
    }
}

abstract class ScopedVisitor<T> extends SqlTokenVisitor<T> {
    @Override
    public <S> T visit(LateralSubSelect lateralSubSelect, S context) {
        this.enteringScope();
        super.visit(lateralSubSelect, context);
        this.exitingScope();
        return null;
    }

    @Override
    public <S> T visit(ParenthesedSelect select, S context) {
        this.enteringScope();
        super.visit(select, context);
        this.exitingScope();
        return null;
    }

    @Override
    public <S> T visit(Select select, S context) {
        this.enteringScope();
        super.visit(select, context);
        this.exitingScope();
        return null;
    }

    @Override
    public <S> T visit(ParenthesedFromItem parenthesis, S context) {
        this.enteringScope();
        super.visit(parenthesis, 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) {
        for (ScopeData data : scopedData) {
            Table resolved = resolveAlias(data.tables, alias);
            if (resolved != null) {
                return resolved;
            }
        }
        return alias;
    }

    public Table resolveAlias(List<Table> tables, Table alias) {
        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) {
        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
~~~sh
employee_id : projects
employee_id : employees
employee_id : tasks
employee_id : employees
name : employees
~~~

I have already asked this question in [stackoverflow](https://stackoverflow.com/questions/79121991/how-to-get-all-column-vs-table-regardless-of-alias-using-jsqlparser) But now I seen specific site for code review I am also posting here also.