Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

subquery with 3 schemas is not returning data

Avatar

Level 4

Hi guys,

I have built this query to join 3 schemas, but when executing is not returning any data, if I use only one sub-query it does work, but adding a second schema to join, it fails.

var query = xtk.queryDef.create(

  <queryDef schema="search:airport" operation="select">

  <where>

      <condition expr="@id_city" setOperator="IN">

                  <subQuery schema="search:city">

                  <select>

                  <node expr="[@id_city]"/>

                  </select>

                  </subQuery>

       </condition>

       <condition expr="@id_country" setOperator="IN">

                  <subQuery schema="search:country">

                  <select>

                  <node expr="[@id_country]"/>

                  </select>

                  </subQuery>

       </condition>

       <condition expr={"@iata_code='" + iata_code + "'"}/>

    </where>

  </queryDef>

);

query.SelectAll(false);

result = query.ExecuteQuery();

Regards,

Raúl

1 Accepted Solution

Avatar

Correct answer by
Level 4

I ended out using a SQL expression and it worked.

var sql = "select ai.iId_aeropuerto, ai.iId_ciudad, ai.iId_pais, ai.sCodigo_iata, ai." + name + ", ci.sCodigo_iata, ci." + name + ", co.iId_region, co.sCodigo_iata, co." + name + " from SearchAirport ai join SearchCity ci on ai.iId_ciudad = ci.iId_ciudad join SearchCountry co on ci.iId_pais = co.iId_pais where ai.sCodigo_iata = '" + iataCode + "'";

var format = "resSQLResult, @idAirport:string, @idCity:string, @idCountry:string, @iataAirport:string, @nameAirport:string, @iataCity:string, @nameCity:string, @idRegion:string, @iataCountry:string, @nameCountry:string";

var sqlRes = sqlSelect(format, sql);

View solution in original post

2 Replies

Avatar

Community Advisor

Hi,

Does it work if you use setOperator="EXISTS"? You can reference the outer query with its xpath in the exists, e.g. expr="@id_country = [airport/@id_country]".

Thanks,

-Jon

Avatar

Correct answer by
Level 4

I ended out using a SQL expression and it worked.

var sql = "select ai.iId_aeropuerto, ai.iId_ciudad, ai.iId_pais, ai.sCodigo_iata, ai." + name + ", ci.sCodigo_iata, ci." + name + ", co.iId_region, co.sCodigo_iata, co." + name + " from SearchAirport ai join SearchCity ci on ai.iId_ciudad = ci.iId_ciudad join SearchCountry co on ci.iId_pais = co.iId_pais where ai.sCodigo_iata = '" + iataCode + "'";

var format = "resSQLResult, @idAirport:string, @idCity:string, @idCountry:string, @iataAirport:string, @nameAirport:string, @iataCity:string, @nameCity:string, @idRegion:string, @iataCountry:string, @nameCountry:string";

var sqlRes = sqlSelect(format, sql);