如何用这个表结构从MySQL实现结果？

Question

I have a table that looks like this:

id  content_id  fields_id   value_int   value_string    value_decimal

351 3           1                       204 
352 3           2                       Western Cape    
353 3           3                       Cape Town   

365 4           1                       205 
366 4           2                       District of Columbia    
367 4           3                       Washington

365 4           1                       204 
366 4           2                       Eastern Cape    
367 4           3                       Jeffreys Bay    

This repeats for thousands of listings.

Now what I want to do, is write a query that I feed the ID (i.e 204, which is a country ID) - fields_id 1.

Then it must give me a result of a distinct list of all the states / provinces (the value string of fields_id 2) for country ID 204, for instance.

My MySQL skill are on the basic side, so could you please give an idea of how to achieve this in a query?

Answer 1

This SQL statement will do what you want:

SELECT w.id, w.value_string  
FROM Table1 o
LEFT JOIN Table1 w ON w.id = (o.id + 1)
WHERE o.value_string = '204'