subquery with 3 schemas is not returning data | Community
Skip to main content
RaulOcana
RaulOcana
Level 3
June 12, 2019
Solved

subquery with 3 schemas is not returning data

  • June 12, 2019
  • 2 replies
  • 3545 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by RaulOcana

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

    2 replies

    Jonathon_wodnicki
    Community Advisor
    Jonathon_wodnickiCommunity Advisor
    Community Advisor
    June 12, 2019

    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
      RaulOcanaAuthorAccepted solution
      Level 3
      June 12, 2019

      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);
      Terms & ConditionsAccessibility statement

      Loading footer...