gareth asked this 7 years ago

SQL to select rows based on data combinations in a table

I have a table with 3 columns - seq, field and data like below

Seq	Field	Data
1	Continent	Asia
1	Country	India
1	Capital	Delhi
1	Language	Hindi
2	Continent	Europe
2	Country	England
2	Capital	London
2	Language	English

i want to write a query that retrieves a country's details by specifying the country name and the continent of that country.

For examle if I say Continent is Europe and Country is England, the query to retrieve the following records

Seq	Field	Data
2	Continent	Europe
2	Country	England
2	Capital	London
2	Language	English

Vikas 7 years ago

1 like

You need a query with multiple columns and a IN clause:

SELECT seq FROM table_name WHERE (Field, Data) IN ('Continent', 'Europe')

the above query finds records with field and data combination of 'Continent' and 'Europe'.

Similarly you need another query to find the combination 'Country' and 'England'.

You then combine the two queries with an EXISTS clause that will give you the "seq"

Finally you wrap the two queries inside another query that returns all records with the selected "seq" value.

The completed SQL will be like this:

SELECT * FROM table_name WHERE seq IN (SELECT seq FROM table_name WHERE (Field, Data) IN ('Continent', 'Europe') AND EXISTS (SELECT * FROM table_name WHERE (Field, Data) IN ('Country', 'England') ) )