gareth asked this 4 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 4 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') )   )