query multiple filter from a table that data store in single column structure

Question

I have a database that stores all the field data with below structure in single column fieldValue and fieldID. I would like to know for this type of structure how I can have 2 filters in where condition?

for example how I can query all the fieldID2 which is equal to High and all the fieldID11 which is Discrepancy.

database is mysql.

The output will look like below, the output will be like pivot table. please take note that I dont have any problem for write query, I want to know how to have more than one where condition for two columns.

fieldid1     fieldid2     fieldid3     fieldid4
ahmed         high        xxxxxssss    Finance

@Tim Biegeleisen: No, this is a key/value table. It seems to be the typical task to find an item for which you want multiple conditions matched (i.e. find a record with `FieldID = 2 and FieldValue = 'High'` and a record with `FieldID 11 and FieldValue = 'Discrepancy'` for the same `InternalNo`). — Thorsten Kettner, May 03 '18 at 12:08
@Amir: Have you tried anything? Where are you stuck? What does your expected result look like? Are you trying to get all InternalNos that meet both conditions? So the result for the shown data would be 89796? — Thorsten Kettner, May 03 '18 at 12:09
@ThorstenKettner: I tried the way Tim said but I ready to what u said. then I tried to use group and having but I was not successful. however, I didn't spend so much time since I manage it in another way. but I would like to understand how to query key/value table. — Amir, May 03 '18 at 12:17
its even more confusing with your output. What are those column names : fieldid1 ,fieldid2,fieldid3,fieldid4 ?? To which filter correspond your example? Edit this question and clarify please. Not in the comments. — Thomas G, May 03 '18 at 12:21
Possible duplicate : https://stackoverflow.com/questions/1241178/mysql-rows-to-columns — Sandesh Gupta, May 03 '18 at 12:22
@ThomasG: by using inner query those columns will be filter and show. it is like pivot table/ — Amir, May 03 '18 at 12:23
ok that's a pivot table. but it should be clearly expressed in the question, not the comments — Thomas G, May 03 '18 at 12:24
@ThomasG: it just cross my mind :D I know it in Oracle not mysql — Amir, May 03 '18 at 12:24
@SandeshGupta: I know how to query it, my problem is how to put more than one condition in where ... — Amir, May 03 '18 at 12:25
Show your actual SQL if if you know how to query it, that will help us to understand — Thomas G, May 03 '18 at 12:27
@ThomasG: the query will be same as http://stackoverflow.com/questions/1241178/mysql-rows-to-columns — Amir, May 03 '18 at 12:29

score 1 · Accepted Answer · answered May 03 '18 at 13:20

I am not sure what you are asking exactly. Anyway, key/value tables are usually queried by aggregation. So if per InternalNo you want to show the values for FieldIDs 1, 2, 3 and 4, provided their value for FieldID 2 is 'High' and the value for FieldID 11 is 'Discrepancy', you'd do:

select
  internalno, 
  any_value(case when fieldid = 1 then fieldvalue end) as fieldvalue1,
  any_value(case when fieldid = 2 then fieldvalue end) as fieldvalue2,
  any_value(case when fieldid = 3 then fieldvalue end) as fieldvalue3,
  any_value(case when fieldid = 4 then fieldvalue end) as fieldvalue4
from keyvalues
group by internalno
having any_value(case when fieldid = 2 then fieldvalue end) = 'High'
   and any_value(case when fieldid = 11 then fieldvalue end) = 'Discrepancy';

In order to speed this up, you can apply a WHERE clause on the desired FieldIDs;

where fieldid in (1,2,3,4,11)

As to multiple values for one attribute like your FieldID 10 for InternalNo 89796, you'd have to decide which value(s) to get and ANY_VALUE might not suffice for that.

query multiple filter from a table that data store in single column structure

1 Answers1