subquery with 3 schemas is not returning data

[RaulOcana]

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

[Jonathon_wodnicki]

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

[RaulOcana · Accepted solution]

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);