1

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.

enter image description here

Gokhan
  • 453
  • 4
  • 10
  • 1
    Does this answer your question? [SQL query: Simulating an "AND" over several rows instead of sub-querying](https://stackoverflow.com/questions/163887/sql-query-simulating-an-and-over-several-rows-instead-of-sub-querying) – astentx May 18 '23 at 07:08

2 Answers2

1

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

ahmed
  • 9,071
  • 3
  • 9
  • 22
  • 1
    Its worked perfectly. Thanx. What is the ide a of using HAVING COUNT(*)=3. I f i want to add one more condition, should it be HAVING COUNT(*)=4 ? – Gokhan May 18 '23 at 06:51
  • 1
    The use of `COUNT(*) = 3` is to ensure that three rows are returned for each ID group, (one row for each condition), so yes, if you want to add another condition that should be met, use `COUNT(*) = 4` – ahmed May 18 '23 at 06:55
  • 1
    Note that, if an ID could have duplicate attributes, i.e. *more than one attribute with the same color*, then use the `count(distinct attribute) = 3` to ensure that the returned ID group has three different attributes that meet the specified criteria. (I don't think you have this case anyway) – ahmed May 18 '23 at 07:01
  • 1
    Thanx for extra infos . In this table every ID has one attribute. But I'll keep this in mind for the future. – Gokhan May 18 '23 at 07:04
1

One option is to reformat source table so that every "value" fits into its own column (that's what the temp CTE in my query does). Then it is really simple to extract data you need.

Sample data:

SQL> with attribute_tab (id, attribute, value_no, value_text) as
  2    (select 1, 'WIDTH', 6000, null        from dual union all
  3     select 1, 'COLOR', null, 'BLUE'      from dual union all
  4     select 1, 'GLASS', null, 'TEMPERED'  from dual union all
  5     --
  6     select 2, 'WIDTH', 6500, null        from dual union all
  7     select 2, 'COLOR', null, 'YELLOW'    from dual union all
  8     select 2, 'GLASS', null, 'BIRD SAFE' from dual union all
  9     --
 10     select 4, 'WIDTH', 6000, null        from dual union all
 11     select 4, 'COLOR', null, 'BLUE'      from dual union all
 12     select 4, 'GLASS', null, 'TEMPERED'  from dual union all
 13     --
 14     select 6, 'WIDTH', 6500, null        from dual union all
 15     select 6, 'COLOR', null, 'BLUE'      from dual union all
 16     select 6, 'GLASS', null, 'BIRD SAFE' from dual
 17    ),

Query begins here:

 18  temp as
 19    (select id,
 20       max(case when attribute = 'WIDTH' then value_no end) width,
 21       max(case when attribute = 'COLOR' then value_text end) color,
 22       max(case when attribute = 'GLASS' then value_text end) glass
 23     from attribute_tab
 24     group by id
 25    )
 26  select *
 27  from temp
 28  where width = 6000
 29    and color = 'BLUE'
 30    and glass = 'TEMPERED'
 31  order by id;

        ID      WIDTH COLOR     GLASS
---------- ---------- --------- ---------
         1       6000 BLUE      TEMPERED
         4       6000 BLUE      TEMPERED

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57