CODEWITHSUNDEEP
mysqlsqljoinsubquery
bashirudeen ahmad
bashirudeen ahmad

Reputation: 213

Query join same table error

My table of data looks like this:

term_id         description     parent
 3048           India           3047
 3049           Srilanka        3047
 3311           Tamilnadu       3048
 3312           Karnataka       3048
 3313           Kerala          3048
 3314           columbu         3049
 3315           columbu1        3049

I want to get the below table result.

Country      State
 India       Tamilnadu
 India       Karnataka
 India       Kerala
 Srilanka    columbu
 Srilanka    columbu1

I have tried the following but I did not get the exact result. 

SELECT b.description as country, 
  (SELECT a.description 
   FROM `xt_term_taxonomy` a 
   WHERE a.`parent` IN (b.term_id) ) as state 
FROM `xt_term_taxonomy` b 
WHERE a.`taxonomy`

Can you tell me how to update this to get the desired result?

Upvotes: 0

Views: 30

Answers (1)

ollie
ollie

Reputation: 1009

Since you want both parent (country) and child (state), you'll want to JOIN the table together:

SELECT 
    c.description as country, 
    s.description as state
FROM `xt_term_taxonomy` c 
INNER JOIN `xt_term_taxonomy` s 
    on c.term_id = s.parent;

An INNER JOIN will return all countries and their states, if you want all countries even if they don't have a state, then use a LEFT JOIN. Here is a demo.

Upvotes: 1

Related Questions