Implement SQL recursive query in both MySQL an SQL Server?
I have this native query:
//return all children @Query( value = "WITH cte(uuid) AS" + " (" + " SELECT uuid" + " FROM org_unit " + " WHERE uuid = :uuid" + " UNION ALL" + " SELECT ou.uuid" + " FROM org_unit ou" + " JOIN cte ON ou.parent_org_unit_uuid = cte.uuid" + " )" + " SELECT * FROM org_unit WHERE org_unit.uuid IN " + " ( SELECT DISTINCT cte.uuid FROM cte)", nativeQuery = true) Collection<OrgUnitEntity> getAllChildrenWithSelf(@Param("uuid") String uuid);
but the problem is that this query does not work with MySQL. I want to support SQL Server and MySQL.