XQuery - “NOT IN” equivalent not working as expected

134
May 01, 2018, at 05:10 AM

I am new to XQuery (coming from SQL) and I am trying to write a query that selects all results that do not exist in another table.

More specifically I am using this XML database: https://www.dbis.informatik.uni-goettingen.de/Mondial/mondial.xml and I am trying to select all countries that do NOT have any islands

XML Query:

let $islands := doc("mondial.xml")/mondial/island/located/data(@country)
for $country in doc("mondial.xml")/mondial/country
let $c_code := $country/data(@car_code)
let $c_name := data($country/name)
where not($c_code=$islands)
order by $c_name
return $c_name

Same query but in SQL:

SELECT name 
FROM Country 
WHERE code 
NOT IN (SELECT country FROM geo_island);

(relational schema of equivalent SQL database: https://www.dbis.informatik.uni-goettingen.de/Mondial/mondial-RS.pdf )

The correct amount of countries I should get in my results is 120, but instead I get 210 countries. What did I do wrong?

EDIT: If I didnt make it clear before: I wrote the SQL query before I wrote the XQuery query. I am simply trying to translate SQL queries to XQuery queries.

Answer 1

The countries an island is part of are in /mondial/island/@country, not in /mondial/island/located/@country. See for example the entry for Ireland:

<island id="island-Ireland" country="IRL GB" sea="sea-Irische_See sea-Atlantic">
  <name>Ireland</name>
  <islands>British Isles</islands>
  <located country="GB" province="prov-gb-12"/>
  <area>84421</area>
  <latitude>53.5</latitude>
  <longitude>-7.8</longitude>
  <elevation>1041</elevation>
</island>

Both Ireland the country and the UK (i.e. Northern Ireland) are on the island of Ireland, so there are two space-separated entries in /mondial/island[name = 'Ireland']/@country.

You can use fn:tokenize($string[, $separator]) to get all single countries and (for performance) get all unique island-having countries with fn:distinct-values($sequence). The rest can stay the same:

let $doc := doc("mondial.xml")
let $islands := distinct-values($doc/mondial/island/@country/tokenize(.))
for $country in $doc/mondial/country
let $c_code := $country/data(@car_code)
let $c_name := data($country/name)
where not($c_code=$islands)
order by $c_name
return $c_name

This now returns 120 countries as expected.

READ ALSO
Symfony 3.4 getDoctrine() results in error

Symfony 3.4 getDoctrine() results in error

I have seen some posts about this topic but not the answer I am looking forWhen I call $this->getDoctrine(); from a method say public function returnObjectAction(){} and the Class implements use Symfony\Bundle\FrameworkBundle\Controller\Controller...

161
SQL innerjoin convert integer to text

SQL innerjoin convert integer to text

I have an SQL database directorydb that holds two tables: departments and persons

108
display username AFTER user has logged in PHP MySQL

display username AFTER user has logged in PHP MySQL

I've built a simple user register and login system, using PHP and bootstrapI am a static designer so PHP is very new to me and I'm struggling with the syntax and logic so far, but I am enjoying it

131
How to replace JSON key&#39;s value in mysql

How to replace JSON key's value in mysql

I have a mysql JSON column like:

144