mysql: find parent “domain name”

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:

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!

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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

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