logo
down
shadow

Transitive SQL query on same table


Transitive SQL query on same table

By : jdoe
Date : November 22 2020, 07:01 PM
I hope this helps . Hey. consider d following table and data...
code :


Share : facebook icon twitter icon
Recursive query used for transitive closure

Recursive query used for transitive closure


By : Wim
Date : March 29 2020, 07:55 AM
will help you You can simplify in several places (assuming acct_id and parent_id are NOT NULL):
code :
WITH RECURSIVE search_graph AS (
   SELECT parent_id, ARRAY[acct_id] AS path
   FROM   account

   UNION  ALL
   SELECT g.parent_id, sg.path || g.acct_id
   FROM   search_graph sg
   JOIN   account g ON g.acct_id = sg.parent_id 
   WHERE  g.acct_id <> ALL(sg.path)
   )
SELECT path[1] AS child
     , path[array_upper(path,1)] AS parent
     , path
FROM   search_graph
ORDER  BY path;
WITH RECURSIVE search_graph AS (
   SELECT parent_id, ARRAY[acct_id] AS path, acct_id <> parent_id AS keep_going
   FROM   account

   UNION  ALL
   SELECT g.parent_id, sg.path || g.acct_id, g.acct_id <> g.parent_id
   FROM   search_graph sg
   JOIN   account g ON g.acct_id = sg.parent_id 
   WHERE  sg.keep_going
)
SELECT path[1] AS child
     , path[array_upper(path,1)] AS parent
     , path
FROM   search_graph
ORDER  BY path;
Ordering in transitive SPARQL query

Ordering in transitive SPARQL query


By : Nathan
Date : March 29 2020, 07:55 AM
I wish this helpful for you For simple linear paths, you could use the number of hops as a measure for ordering:
code :
PREFIX  ex:   <http://example.com/>

SELECT  ?start
WHERE
  { ?start (ex:contains)+ ?mid .
    ?mid (ex:contains)* ex:step3
  }
GROUP BY ?start
ORDER BY DESC(COUNT(?mid))
------------
| start    |
============
| ex:step0 |
| ex:step1 |
| ex:step2 |
------------
Query for transitive word translation

Query for transitive word translation


By : Stefano Pettini
Date : March 29 2020, 07:55 AM
it helps some times This is a complicated graph walking problem. The idea is to set up a connections2 relationship that has words and languages in both directions, and then use this for walking the graph.
To get all German translations for Polish words, you can use:
code :
with words AS (
      select 1 as id, 'Cat' as word, 'ENG' as lang union all
      select 2, 'Kot', 'POL' union all
      select 3, 'Katze', 'GER'
     ),
     connections as (
      select 1 as id, 1 as word_a, 2 as word_b union all
      select 2 as id, 1 as word_a, 3 as word_b
     ),
     connections2 as (
      select c.word_a as id_a, c.word_b as id_b, wa.lang as lang_a, wb.lang as lang_b
      from connections c join
           words wa
           on c.word_a = wa.id join
           words wb
           on c.word_b = wb.id
      union  -- remove duplicates
      select c.word_b, c.word_a, wb.lang as lang_a, wa.lang as lang_b
      from connections c join
           words wa
           on c.word_a = wa.id join
           words wb
           on c.word_b = wb.id
     ),
     cte as (
      select id as pol_word, id as other_word, 'POL' as other_lang, 1 as lev, ',POL,' as langs
      from words
      where lang = 'POL'
      union all
      select cte.pol_word, c2.id_b, c2.lang_b, lev + 1, langs || c2.lang_b || ','
      from cte join
           connections2 c2
           on cte.other_lang = c2.lang_a
      where langs not like '%,' || c2.lang_b || ',%' or c2.lang_b = 'GER'
     ) 
select *
from cte
where cte.other_lang = 'GER';
select *
from cte
where cte.other_lang = 'GER' and
      cte.lev = (select min(cte2.lev) from cte cte2 where ct2.pol_word = cte.pol_word);
mySQL transitive closure table

mySQL transitive closure table


By : jorge_p
Date : March 29 2020, 07:55 AM
I hope this helps . I know this is old, but I feel you still need an answer on this for others looking, here is how I generated my closure table from my standard adjacency table:
python rdflib transitive query

python rdflib transitive query


By : Moustafa Algendy
Date : March 29 2020, 07:55 AM
With these it helps After checking more of the rdflib documentation I figured out the following solution:
Related Posts Related Posts :
  • Refactoring SQL
  • Create view or SP, only if the DB contains a pattern
  • Sql joining a table
  • Optimize the sql query
  • SQL server management studio Express
  • insert into select from in a table with identity column
  • Is is faster to filter and get data or filter then get data?
  • SQL server 2005, syntax error
  • SQL SELECT Join?
  • How to limit results by SUM
  • Need Help with SQL Subquery
  • SQL Join Tables
  • pipelined function
  • how to generate primary key values while inserting data into table through pl/sql stored procedure
  • Calling stored procedure from another stored procedure and returning result as new columns
  • Are these tables respect the 3NF Database Normalization?
  • Complex query making site extremely slow
  • SQL Full-Text Indexing Issue
  • finding a relationship accross many tables
  • Using Parameter Values In SQL Statement
  • oracle sql plus spool
  • Multiple rows update trigger
  • SQL - Conditionally evaluate WHERE clauses
  • Locking User account created under Windows Authentication in SQL Server
  • trigger execution against condition satisfaction
  • oracle sql developer is truncating my results
  • “Function” calling inside stored procedure
  • Excel VBA to Update SQL Table
  • I've caught an exception!! Now what?
  • How to select the last two records for each topic_id in MySQL
  • SQL Server 2005 triggered audit tables moved to SQL Server 2008, now trigger does not respond when trying to insert row
  • Using NHibernate to insert/update using a SQL server-side DEFAULT value
  • How to convert column type from varchar to date in PostgreSQL?
  • Combining the UNIQUE and CHECK constraints
  • MS SQL Server decimal data type rounds up
  • sp_addlinkedserver on sql server 2005 giving problem
  • Strange use of the index in Mysql
  • Oracle - pl sql selecting from SYS_REFCURSOR
  • Broken count(*) after adding LEFT JOIN
  • Product Catalog Schema design
  • ODBC Connection String Problem
  • What's a reasonable number of rows and tables to be able to join in MySQL?
  • CFQuery where param
  • Split a Column in SQL Server
  • What does the % symbol do at the end of a string in SQL?
  • sql server 2005 express - Invalid Object Name error
  • Merge overlapping date intervals
  • Loop Control within a DataflowTask in ETL
  • SQL | How to always round up regardless of the last integer value, even when that may be 0
  • how to convert any char to - in sql server?
  • how to create function in postgres
  • Need to speed up the results of this SQL statement. Any advice?
  • How do I retrieve 10 days data up to date 'XYZ' from mysql with Perl when some days data are missing, i.e. public holida
  • How to export data with Oracle SQL Developer?
  • Aggregate SQL column values by time period
  • Are soft deletes a good idea?
  • DBCC SHRINKFILE 1 sproc for multiple databases
  • SQL VBA syntax issue
  • How to select a subset of results from a select statement
  • Oracle SQL: ROLLUP not summing correctly
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk