mysql: find parent "domain name"

All we need is an easy explanation of the problem, so here it is.

My SQL query returns "subquery returns more than 1 row" error. To avoid "X-Y" problem I will omit what my query is, just focus on the data structure and what I want to achieve.

Sample Data:

fqdn
location.country1.city1.district1
location.country1.city1
location.country1
location.country2.city2.district2
location.country2.city2
location.country2
location.country3.city3.district3
location.country3.city3
location.country3

by some sub-query I got two row of data, say location.country1.district1 and location.country2.district2. My purpose is to get all rows of their "parents", i.e., location.country1, location.country1.city1, location.country2, … etc.

My query looks like:

SELECT fqdn FROM table WHERE CONCAT(fqdn, '.%') LIKE (sub-query which returns multiple rows)        

Which of course generated that error. So, I can do this in pure (one-line) SQL query?

Thanks!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

SELECT /* DISTINCT */            -- uncomment if you need unique fqdn only
       fqdn_table.fqdn 
  /* , subquery.outputcolumn */  -- uncomment if you want to know 
                                 -- what subq. row matches to what fqdn
FROM fqdn_table 
JOIN (subquery which returns multiple rows) AS subquery
WHERE LOCATE(fqdn_table.fqdn, subquery.outputcolumn) = 1

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply