How to select multiple entries in a single query.
The db table structure represents an excel like spreadsheet. Each observation is a workbook.
Work book 1
1 2
3 4
Work book 2
5 6
7 8
with each db row representing a single cell and observations in separate workbooks. The table definition is:
observation integer
row_number integer
col_number integer
value integer
The database then looks like this:
observation row_number col_number value
1 1 1 1
1 1 2 2
1 2 1 3
1 2 2 4
2 1 1 5
2 1 2 6
2 2 1 7
2 2 2 8
The question is how to create a single query to:
select observation, value as value1 from database where row_number = 1 and col_number = 2,
select value as value2 from database where row_number = 2 and col_number = 1;
to create:
observation value1 value2
1 2 3
2 6 7
I have tried joins and subqueries.
The basic answer is with a self-join. The table name 'database' is pretty objectionable: I'm going to call it 'spreadsheet'.
SELECT r1.observation, r1.value AS value1, r2.value AS value2
FROM spreadsheet AS r1
JOIN spreadsheet AS r2
ON r1.observation = r2.observation
WHERE r1.row_number = 1
AND r1.col_number = 2
AND r2.row_number = 2
AND r2.col_number = 1
There are plenty of other ways of writing the same query. One of them is:
SELECT r1.observation, r1.value1, r2.value2
FROM (SELECT observation, value AS value1
FROM spreadsheet
WHERE r1.row_number = 1
AND r1.col_number = 2
) AS r1
JOIN (SELECT observation, value AS value2
FROM spreadsheet
WHERE r2.row_number = 2
AND r2.col_number = 1
) AS r2
ON r1.observation = r2.observation
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments