I have a table as below
create table auto
(
ID number,
SEQ number,
COLUMN_NME varchar2(10),
RELATIONAL_OPERATOR varchar2(20),
COLUMN_VALUE varchar2(20)
);
insert into auto SELECT 1,1,'HONDA','=','ACCORD' FROM DUAL;
insert into auto SELECT 1,2,'ENGINE','=','V6' FROM DUAL;
insert into auto SELECT 1,3,'COLOR','=','BLACK' FROM DUAL;
insert into auto SELECT 1,4,'COLOR','=','BLUE' FROM DUAL;
insert into auto SELECT 2,1,'TOYOTA','=','SIENNA' FROM DUAL;
insert into auto SELECT 2,2,'COLOR','=','BLACK' FROM DUAL;
insert into auto SELECT 2,3,'ENGINE','<>','V6' FROM DUAL;
insert into auto SELECT 3,1,'HONDA','=','ODYSSEY' FROM DUAL;
insert into auto SELECT 3,1,'COLOR','<>','BLACK' FROM DUAL;
insert into auto SELECT 3,1,'ENGINE','=','V6' FROM DUAL;
insert into auto SELECT 3,1,'HONDA','=','CRV' FROM DUAL;
I need the results where
- column_nme = ‘HONDA’, COLUMN_VALUE = ‘ACCORD’
- COLUMN_NME= ‘COLOR’, COLUMN_VALUE = ‘BLACK’
- COLUMN_NME = ‘ENGINE’ , COLUMN_VALUE = ‘V6’
(CONSIDERING ALL RELATIONAL_OPERATOR IS ‘=’)
I tried writing using case statement
select ID,SEQ,case when column_nme = 'HONDA' and column_value = 'ACCORD' THEN 1 ELSE 0 END HONDA,
CASE WHEN COLUMN_NME = 'ENGINE' AND COLUMN_VALUE = 'V6' THEN 1 ELSE 0 END ENGINE,
CASE WHEN COLUMN_NME = 'COLOR' AND COLUMN_VALUE = 'BLACK' THEN 1 ELSE 0 END COLOR
FROM AUTO;
I need the ID of this table where HONDA = 1, ENGINE = 1 AND COLOR = 1
Your table auto is an EAV (entity attribute value) table, and you want the IDs matching all of three criteria:
- HONDA = ACCORD
- COLOR = BLACK
- ENGINE = V6
The typical approach to query EAV tables is via aggregation: Get all IDs having all required matches:
select id
from auto
group by id
having count(case when column_nme = 'HONDA' and relational_operator = '=' and column_value = 'ACCORD' then 1 end) > 0
and count(case when column_nme = 'COLOR' and relational_operator = '=' and column_value = 'BLACK' then 1 end) > 0
and count(case when column_nme = 'ENGINE' and relational_operator = '=' and column_value = 'V6' then 1 end) > 0;
Demo: https://dbfiddle.uk/NEDty7pP