CODEWITHSUNDEEP
sqlstored-procedures
Mostafa
Mostafa

Reputation: 680

select multi row inside of a table (not same condition)

I try to explain an issue I have faced nowadays. Actually I have designed a table in order to track the changes applying by users inside of a depot of NLP engine.

I have two table named Token And Lexeme. each token has an id that directly connect to a row of lexeme table. and always I can find the latest and updated lexemes by looking up to token table.

here is their scheme:

Token Table:
+-----+----------+----------+
| Id  | token    |LexemeId* |
+-----+----------+----------+
LexemeId refers to a row inside of lexeme table.

Lexeme Table:

+-----+---------------------+-------------+
| Id  | some information    |UpdatedFrom* |  
+-----+---------------------+-------------+
* UpdatedFrom field refers another row inside of Lexeme Table.

Null means there is no more rows related to this token(lexeme).

an example:

Token Table:
+-----+----------+----------+
| 0   | A        |4         |
| 1   | B        |1         |
+-----+----------+----------+


Lexeme Table:

+-----+----------------------+-------------+
| 0   | A information#1      |NULL         |  
| 1   | B information        |NULL         |  
| 2   | A information#2      |0            |  
| 3   | A information#3      |2            |
| 4   | A information#4      |3            |    
+-----+----------------------+-------------+

I hope I could clear the air. I want to write a store procedure to collect all records related to each token. for example for token 'A', I'm expected to have an array (or data table) looks like this:

+-----+----------------------+-------------+
| id  | informations         | updated from|
+-----+----------------------+-------------+
| 0   | A information#1      |NULL         |  
| 2   | A information#2      |0            |  
| 3   | A information#3      |2            |
| 4   | A information#4      |3            |    
+-----+----------------------+-------------+

anybody has any idea to help me....

my knowledge on sql transcript is summarized to Update, Insert and select statements, not more!

thanks in advanced...

Upvotes: 0

Views: 125

Answers (1)

user359040
user359040

Reputation:

Assuming this is in an RDBMS that supports recursive CTEs, try:

with cte as 
(select t.id TokenId, t.token, l.Id, l.SomeInformation, l.UpdatedFrom
 from Token t
 join Lexeme l on t.LexemeId = l.id
 union all
 select t.TokenId, t.token, l.Id, l.SomeInformation, l.UpdatedFrom
 from cte t
 join Lexeme l on t.UpdatedFrom = l.id)
select Id, SomeInformation, UpdatedFrom
from cte
where TokenId=0 /* token = 'A' */

SQLFiddle here.

Upvotes: 2

Related Questions