SQL. How to find all rows that contains text like current row + all parents rows
I have table, that called addressobject.
addressobject (
aoid character varying(36), //it is unique for table
aoguid character varying(36), //it is not unique value.
//Has equal value for the same address objects
parent_guid character varying(36), //link to parent aoguid
currstatus integer // if this equal 0, then address object is actual.
//Only one addressobject's currstatus is equal to 0 in group of same aoguids
name character varying(120) //I think it is clear
aolevel //level in hierarchy
)
I need to find all rows, that contains passed text.
Example, for string 'regionname areaname cityname %notCompletedStreetName%' I need to return rows that contains similar text after concatenation name with his parent name and parents of all his parents before parent_guid is not null
UPD
OK There is example of table. I make aoid and aoguid like integer to simplify, by actually it has uuid presented to character varying(36):
aoid | aoguid | parent_guid | currstatus | aolevel | name
1 | 100 | | 0 | 1 |'Dagestan'
2 | 200 | 100 | 1 | 3 |'Makhachkala' //outdated row
3 | 200 | 100 | 0 | 3 |'Makhachkala'
4 | 400 | 200 | 1 | 4 |'Gamidov' //outdated row
5 | 500 | 200 | 0 | 4 |'Gamburger'
6 | 600 | 200 | 0 | 4 |'Burger'
7 | 400 | 200 | 0 | 4 |'Gamidov'
8 | 800 | 100 | 0 | 3 |'Derbent'
If I have passed string like 'Dagestan Makhachkala Gam', where 'Gam' is beginning of the not completed street name, 'Makhachkala' and 'Dagestan' is completed name of city and region respectively, area name in this case not exist. In this case it should return rows with aoid 5 and 7.
Input 'Dagestan Makhachkala Gam', Rows: 5, 7
Input 'Dagestan Derbent', Rows: 8
Input 'Dagestan Makhach', Rows: 3
Input 'Dagestan Makhachkala Burg', Rows: 6
etc..
UPDEND
I hope I clearly explained the problem :
sql postgresql recursive-query
add a comment |
I have table, that called addressobject.
addressobject (
aoid character varying(36), //it is unique for table
aoguid character varying(36), //it is not unique value.
//Has equal value for the same address objects
parent_guid character varying(36), //link to parent aoguid
currstatus integer // if this equal 0, then address object is actual.
//Only one addressobject's currstatus is equal to 0 in group of same aoguids
name character varying(120) //I think it is clear
aolevel //level in hierarchy
)
I need to find all rows, that contains passed text.
Example, for string 'regionname areaname cityname %notCompletedStreetName%' I need to return rows that contains similar text after concatenation name with his parent name and parents of all his parents before parent_guid is not null
UPD
OK There is example of table. I make aoid and aoguid like integer to simplify, by actually it has uuid presented to character varying(36):
aoid | aoguid | parent_guid | currstatus | aolevel | name
1 | 100 | | 0 | 1 |'Dagestan'
2 | 200 | 100 | 1 | 3 |'Makhachkala' //outdated row
3 | 200 | 100 | 0 | 3 |'Makhachkala'
4 | 400 | 200 | 1 | 4 |'Gamidov' //outdated row
5 | 500 | 200 | 0 | 4 |'Gamburger'
6 | 600 | 200 | 0 | 4 |'Burger'
7 | 400 | 200 | 0 | 4 |'Gamidov'
8 | 800 | 100 | 0 | 3 |'Derbent'
If I have passed string like 'Dagestan Makhachkala Gam', where 'Gam' is beginning of the not completed street name, 'Makhachkala' and 'Dagestan' is completed name of city and region respectively, area name in this case not exist. In this case it should return rows with aoid 5 and 7.
Input 'Dagestan Makhachkala Gam', Rows: 5, 7
Input 'Dagestan Derbent', Rows: 8
Input 'Dagestan Makhach', Rows: 3
Input 'Dagestan Makhachkala Burg', Rows: 6
etc..
UPDEND
I hope I clearly explained the problem :
sql postgresql recursive-query
2
No, sorry, it's not clearly explained. The standard practice to help here is to give example data, example parameters, and example expected results.
– MatBailie
Nov 15 '18 at 13:56
Create a sub querry to perform the search for the records you want, then wrap this with an outer query which counts the rows.
– SPlatten
Nov 15 '18 at 13:58
Give us a list of example data and the passed text, then show us which rows of your sample data you want returned.
– haag1
Nov 15 '18 at 14:21
unical == unique?
– Joakim Danielson
Nov 15 '18 at 14:45
add a comment |
I have table, that called addressobject.
addressobject (
aoid character varying(36), //it is unique for table
aoguid character varying(36), //it is not unique value.
//Has equal value for the same address objects
parent_guid character varying(36), //link to parent aoguid
currstatus integer // if this equal 0, then address object is actual.
//Only one addressobject's currstatus is equal to 0 in group of same aoguids
name character varying(120) //I think it is clear
aolevel //level in hierarchy
)
I need to find all rows, that contains passed text.
Example, for string 'regionname areaname cityname %notCompletedStreetName%' I need to return rows that contains similar text after concatenation name with his parent name and parents of all his parents before parent_guid is not null
UPD
OK There is example of table. I make aoid and aoguid like integer to simplify, by actually it has uuid presented to character varying(36):
aoid | aoguid | parent_guid | currstatus | aolevel | name
1 | 100 | | 0 | 1 |'Dagestan'
2 | 200 | 100 | 1 | 3 |'Makhachkala' //outdated row
3 | 200 | 100 | 0 | 3 |'Makhachkala'
4 | 400 | 200 | 1 | 4 |'Gamidov' //outdated row
5 | 500 | 200 | 0 | 4 |'Gamburger'
6 | 600 | 200 | 0 | 4 |'Burger'
7 | 400 | 200 | 0 | 4 |'Gamidov'
8 | 800 | 100 | 0 | 3 |'Derbent'
If I have passed string like 'Dagestan Makhachkala Gam', where 'Gam' is beginning of the not completed street name, 'Makhachkala' and 'Dagestan' is completed name of city and region respectively, area name in this case not exist. In this case it should return rows with aoid 5 and 7.
Input 'Dagestan Makhachkala Gam', Rows: 5, 7
Input 'Dagestan Derbent', Rows: 8
Input 'Dagestan Makhach', Rows: 3
Input 'Dagestan Makhachkala Burg', Rows: 6
etc..
UPDEND
I hope I clearly explained the problem :
sql postgresql recursive-query
I have table, that called addressobject.
addressobject (
aoid character varying(36), //it is unique for table
aoguid character varying(36), //it is not unique value.
//Has equal value for the same address objects
parent_guid character varying(36), //link to parent aoguid
currstatus integer // if this equal 0, then address object is actual.
//Only one addressobject's currstatus is equal to 0 in group of same aoguids
name character varying(120) //I think it is clear
aolevel //level in hierarchy
)
I need to find all rows, that contains passed text.
Example, for string 'regionname areaname cityname %notCompletedStreetName%' I need to return rows that contains similar text after concatenation name with his parent name and parents of all his parents before parent_guid is not null
UPD
OK There is example of table. I make aoid and aoguid like integer to simplify, by actually it has uuid presented to character varying(36):
aoid | aoguid | parent_guid | currstatus | aolevel | name
1 | 100 | | 0 | 1 |'Dagestan'
2 | 200 | 100 | 1 | 3 |'Makhachkala' //outdated row
3 | 200 | 100 | 0 | 3 |'Makhachkala'
4 | 400 | 200 | 1 | 4 |'Gamidov' //outdated row
5 | 500 | 200 | 0 | 4 |'Gamburger'
6 | 600 | 200 | 0 | 4 |'Burger'
7 | 400 | 200 | 0 | 4 |'Gamidov'
8 | 800 | 100 | 0 | 3 |'Derbent'
If I have passed string like 'Dagestan Makhachkala Gam', where 'Gam' is beginning of the not completed street name, 'Makhachkala' and 'Dagestan' is completed name of city and region respectively, area name in this case not exist. In this case it should return rows with aoid 5 and 7.
Input 'Dagestan Makhachkala Gam', Rows: 5, 7
Input 'Dagestan Derbent', Rows: 8
Input 'Dagestan Makhach', Rows: 3
Input 'Dagestan Makhachkala Burg', Rows: 6
etc..
UPDEND
I hope I clearly explained the problem :
sql postgresql recursive-query
sql postgresql recursive-query
edited Nov 15 '18 at 15:45
Арслан Халиков
asked Nov 15 '18 at 13:53
Арслан ХаликовАрслан Халиков
12
12
2
No, sorry, it's not clearly explained. The standard practice to help here is to give example data, example parameters, and example expected results.
– MatBailie
Nov 15 '18 at 13:56
Create a sub querry to perform the search for the records you want, then wrap this with an outer query which counts the rows.
– SPlatten
Nov 15 '18 at 13:58
Give us a list of example data and the passed text, then show us which rows of your sample data you want returned.
– haag1
Nov 15 '18 at 14:21
unical == unique?
– Joakim Danielson
Nov 15 '18 at 14:45
add a comment |
2
No, sorry, it's not clearly explained. The standard practice to help here is to give example data, example parameters, and example expected results.
– MatBailie
Nov 15 '18 at 13:56
Create a sub querry to perform the search for the records you want, then wrap this with an outer query which counts the rows.
– SPlatten
Nov 15 '18 at 13:58
Give us a list of example data and the passed text, then show us which rows of your sample data you want returned.
– haag1
Nov 15 '18 at 14:21
unical == unique?
– Joakim Danielson
Nov 15 '18 at 14:45
2
2
No, sorry, it's not clearly explained. The standard practice to help here is to give example data, example parameters, and example expected results.
– MatBailie
Nov 15 '18 at 13:56
No, sorry, it's not clearly explained. The standard practice to help here is to give example data, example parameters, and example expected results.
– MatBailie
Nov 15 '18 at 13:56
Create a sub querry to perform the search for the records you want, then wrap this with an outer query which counts the rows.
– SPlatten
Nov 15 '18 at 13:58
Create a sub querry to perform the search for the records you want, then wrap this with an outer query which counts the rows.
– SPlatten
Nov 15 '18 at 13:58
Give us a list of example data and the passed text, then show us which rows of your sample data you want returned.
– haag1
Nov 15 '18 at 14:21
Give us a list of example data and the passed text, then show us which rows of your sample data you want returned.
– haag1
Nov 15 '18 at 14:21
unical == unique?
– Joakim Danielson
Nov 15 '18 at 14:45
unical == unique?
– Joakim Danielson
Nov 15 '18 at 14:45
add a comment |
1 Answer
1
active
oldest
votes
First you can create the hierarchy with
SELECT aoid
FROM addressobject region
LEFT JOIN addressobject area
ON
region.aoguid = area.parent_guid
LEFT JOIN addressobject city
ON
area.aoguid = city.parent_guid
LEFT JOIN addressobject street
ON
city.aoguid = street.parent_guid
Next you have to search
WHERE
region.name LIKE REGIONNAME
AND (NOT area.aoguid OR area.name LIKE AREANAME
AND (NOT city ...
No, it's wrong, cause: 1. Sometimes hierarchy is indirect. After city comes region, not area; 2. Some addressobject's name consists of more than one word
– Арслан Халиков
Nov 15 '18 at 16:21
That's an idea and not the final solution. You didn't specif indirect hierarchy or names consisting of more than one word. How can someone answer your question if you don't give such information? You should preprocess the string so that you have each part in one variable. Maybe you should use different sql statements depending on the input.
– Thomas Sablik
Nov 15 '18 at 18:04
My mistake, sorry
– Арслан Халиков
Nov 15 '18 at 20:16
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321007%2fsql-how-to-find-all-rows-that-contains-text-like-current-row-all-parents-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
First you can create the hierarchy with
SELECT aoid
FROM addressobject region
LEFT JOIN addressobject area
ON
region.aoguid = area.parent_guid
LEFT JOIN addressobject city
ON
area.aoguid = city.parent_guid
LEFT JOIN addressobject street
ON
city.aoguid = street.parent_guid
Next you have to search
WHERE
region.name LIKE REGIONNAME
AND (NOT area.aoguid OR area.name LIKE AREANAME
AND (NOT city ...
No, it's wrong, cause: 1. Sometimes hierarchy is indirect. After city comes region, not area; 2. Some addressobject's name consists of more than one word
– Арслан Халиков
Nov 15 '18 at 16:21
That's an idea and not the final solution. You didn't specif indirect hierarchy or names consisting of more than one word. How can someone answer your question if you don't give such information? You should preprocess the string so that you have each part in one variable. Maybe you should use different sql statements depending on the input.
– Thomas Sablik
Nov 15 '18 at 18:04
My mistake, sorry
– Арслан Халиков
Nov 15 '18 at 20:16
add a comment |
First you can create the hierarchy with
SELECT aoid
FROM addressobject region
LEFT JOIN addressobject area
ON
region.aoguid = area.parent_guid
LEFT JOIN addressobject city
ON
area.aoguid = city.parent_guid
LEFT JOIN addressobject street
ON
city.aoguid = street.parent_guid
Next you have to search
WHERE
region.name LIKE REGIONNAME
AND (NOT area.aoguid OR area.name LIKE AREANAME
AND (NOT city ...
No, it's wrong, cause: 1. Sometimes hierarchy is indirect. After city comes region, not area; 2. Some addressobject's name consists of more than one word
– Арслан Халиков
Nov 15 '18 at 16:21
That's an idea and not the final solution. You didn't specif indirect hierarchy or names consisting of more than one word. How can someone answer your question if you don't give such information? You should preprocess the string so that you have each part in one variable. Maybe you should use different sql statements depending on the input.
– Thomas Sablik
Nov 15 '18 at 18:04
My mistake, sorry
– Арслан Халиков
Nov 15 '18 at 20:16
add a comment |
First you can create the hierarchy with
SELECT aoid
FROM addressobject region
LEFT JOIN addressobject area
ON
region.aoguid = area.parent_guid
LEFT JOIN addressobject city
ON
area.aoguid = city.parent_guid
LEFT JOIN addressobject street
ON
city.aoguid = street.parent_guid
Next you have to search
WHERE
region.name LIKE REGIONNAME
AND (NOT area.aoguid OR area.name LIKE AREANAME
AND (NOT city ...
First you can create the hierarchy with
SELECT aoid
FROM addressobject region
LEFT JOIN addressobject area
ON
region.aoguid = area.parent_guid
LEFT JOIN addressobject city
ON
area.aoguid = city.parent_guid
LEFT JOIN addressobject street
ON
city.aoguid = street.parent_guid
Next you have to search
WHERE
region.name LIKE REGIONNAME
AND (NOT area.aoguid OR area.name LIKE AREANAME
AND (NOT city ...
answered Nov 15 '18 at 16:05
Thomas SablikThomas Sablik
2,61811129
2,61811129
No, it's wrong, cause: 1. Sometimes hierarchy is indirect. After city comes region, not area; 2. Some addressobject's name consists of more than one word
– Арслан Халиков
Nov 15 '18 at 16:21
That's an idea and not the final solution. You didn't specif indirect hierarchy or names consisting of more than one word. How can someone answer your question if you don't give such information? You should preprocess the string so that you have each part in one variable. Maybe you should use different sql statements depending on the input.
– Thomas Sablik
Nov 15 '18 at 18:04
My mistake, sorry
– Арслан Халиков
Nov 15 '18 at 20:16
add a comment |
No, it's wrong, cause: 1. Sometimes hierarchy is indirect. After city comes region, not area; 2. Some addressobject's name consists of more than one word
– Арслан Халиков
Nov 15 '18 at 16:21
That's an idea and not the final solution. You didn't specif indirect hierarchy or names consisting of more than one word. How can someone answer your question if you don't give such information? You should preprocess the string so that you have each part in one variable. Maybe you should use different sql statements depending on the input.
– Thomas Sablik
Nov 15 '18 at 18:04
My mistake, sorry
– Арслан Халиков
Nov 15 '18 at 20:16
No, it's wrong, cause: 1. Sometimes hierarchy is indirect. After city comes region, not area; 2. Some addressobject's name consists of more than one word
– Арслан Халиков
Nov 15 '18 at 16:21
No, it's wrong, cause: 1. Sometimes hierarchy is indirect. After city comes region, not area; 2. Some addressobject's name consists of more than one word
– Арслан Халиков
Nov 15 '18 at 16:21
That's an idea and not the final solution. You didn't specif indirect hierarchy or names consisting of more than one word. How can someone answer your question if you don't give such information? You should preprocess the string so that you have each part in one variable. Maybe you should use different sql statements depending on the input.
– Thomas Sablik
Nov 15 '18 at 18:04
That's an idea and not the final solution. You didn't specif indirect hierarchy or names consisting of more than one word. How can someone answer your question if you don't give such information? You should preprocess the string so that you have each part in one variable. Maybe you should use different sql statements depending on the input.
– Thomas Sablik
Nov 15 '18 at 18:04
My mistake, sorry
– Арслан Халиков
Nov 15 '18 at 20:16
My mistake, sorry
– Арслан Халиков
Nov 15 '18 at 20:16
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53321007%2fsql-how-to-find-all-rows-that-contains-text-like-current-row-all-parents-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
No, sorry, it's not clearly explained. The standard practice to help here is to give example data, example parameters, and example expected results.
– MatBailie
Nov 15 '18 at 13:56
Create a sub querry to perform the search for the records you want, then wrap this with an outer query which counts the rows.
– SPlatten
Nov 15 '18 at 13:58
Give us a list of example data and the passed text, then show us which rows of your sample data you want returned.
– haag1
Nov 15 '18 at 14:21
unical == unique?
– Joakim Danielson
Nov 15 '18 at 14:45