Search code examples
sqlabbreviationtablename

How to combine asterix syntax with table abbreviations

Is it possible to combine * syntax with table abbreviations?

I want to do something like:

"SELECT subfunds.* FROM subfunds S" +
" INNER JOIN funds F ON S.id_fund = F.id" +
" WHERE F.fund_short IN('" + stSQLFundList + "')"

The above code gets a syntax error

"invalid reference to FROM-clause entry for table "subfunds".

I already found that if I do

"SELECT * FROM subfunds S" +
" INNER JOIN funds F ON S.id_fund = F.id" +
" WHERE F.fund_short IN('" + stSQLFundList + "')"

then I get all fields from both tables rather than from the subfunds table only.

So how do I get all fields from the first table (and none of the other tables' fields) in my answer set while also being able to use the single-letter table abbreviations?

Solution

  • Change your code to this and you will get all fields from subfunds.

    "SELECT S.* FROM subfunds S" +
" INNER JOIN funds F ON S.id_fund = F.id" +
" WHERE F.fund_short IN('" + stSQLFundList + "')"

    If you are using an alias, then you want to reference that table by it's alias.