Oracle SQL select from multiple condition but with has same id's

byMR

May 18, 2023

I will retrieve data fom oracle database. It has table as you see in picture below. (Table Name is ATTRIBUTE_TAB)

I want to query:

if (VALUE_NO=’6000′ VALUE_TEXT=’TEMPERED’ VALUE_TEXT=BLUE) but these rows has to be same id.

I explained in picture below.

My Query is

SELECT * FROM ATTRIBUTE_TAB
WHERE VALUE_NO='6000' OR VALUE_TEXT='TEMPERED'OR VALUE_TEXT='BLUE

but I couldn’t integrate the id status into the code.

>Solution :

You can use aggregation as the following:

SELECT * FROM ATTRIBUTE_TAB
  WHERE ID IN
  (
    SELECT ID FROM ATTRIBUTE_TAB
    WHERE VALUE_NO='6000' OR VALUE_TEXT='TEMPERED'OR VALUE_TEXT='BLUE'
    GROUP BY ID
    HAVING COUNT(*) = 3 -- or maybe HAVING COUNT(DISTINCT ATTRIBUTE) = 3 if duplicates are possible 
  )
ORDER BY ID, ATTRIBUTE DESC

demo