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:

    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!



  • 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
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2