CODEWITHSUNDEEP
phpmysql
Jacques
Jacques

Reputation: 1709

How to achieve a result from MySQL with this table structure?

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?

Upvotes: 0

Views: 38

Answers (2)

Nikolay Yenbakhtov
Nikolay Yenbakhtov

Reputation: 443

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'

Upvotes: 1

CapeStar
CapeStar

Reputation: 147

Try This.

Select distinct content_id from table_name where SUBSTRING('value_string',1,3)='204';

it gives you all ids associated with your desire info.

Upvotes: 0

Related Questions