Oracle Query : One to Many Relationship

Question

In my application there are one-to-many relationship as explained below.

Table one : Application

app_id	app_name
1	ABC
2	XYZ

Table two : Application_attribute [One application can have multiple attribute and variable list of attribute]

App_attr_id	app_id	attr_name	attr_value
1	1	attr1	white
2	1	attr2	12
3	1	attr3	45
4	2	attr1	red
5	2	attr2	12
6	2	attr4	45
7	2	attr7	62

Each application can have variable list of attributes.

Query Requirement

I want to fetch list of application based on multiple attribute.

Example get list of application whose attributes are attr1=white,attr2=12,attr3=45

In above case, problem can be solved by joining application table with application_attribute table 3 times but attribute will vary per application so it will not be generic solution.

Query Solution for my requirement

SELECT a.*
  FROM application a,
       application_attribute at1,
       application_attribute at2,
       application_attribute at3
 WHERE a.app_id = at1.app_id
   AND a.app_id = at2.app_id
   AND a.app_id = at3.app_id
   AND at1.attr_name = 'attr1'
   AND at1.attr_value = 'white'
   AND at2.attr_name = 'attr2'
   AND at2.attr_value = '12'
   AND at3.attr_name = 'attr3'
   AND at3.attr_value = '45'

Expected Result

app_id	app_name
1	ABC

One option is two create dynamic query. Is it possible to write generic query which can be used to search n number of attribute like 3,4,5..n ?

Answer 1

You want to select applications where exist certain attributes. So, select from the application table and have a where clause checkink the existence of the attributes with EXISTS or IN:

select * 
from aplication
where app_id in (select app_id from application_attribute where attr_name = 'attr1' and attr_value = 'white')
  and app_id in (select app_id from application_attribute where attr_name = 'attr2' and attr_value = '12')
  and app_id in (select app_id from application_attribute where attr_name = 'attr3' and attr_value = '45')
order by app_id;

As to generic: Simply build the query with a programming language and a loop over the desired attributes. In Oracle you can use PL/SQL for this.

Answer 2

You can use EXISTS and a HAVING clause in the correlated sub-query:

SELECT *
FROM   application a
WHERE  EXISTS (
         SELECT 1
         FROM   application_attribute aa
         WHERE  a.app_id = aa.app_id
         AND    (aa.attr_name, aa.attr_value)
                  IN (('attr1', 'white'), ('attr2', '12'), ('attr3', '45'))
         HAVING COUNT(/*DISTINCT*/ aa.attr_name) = 3 
       )

Note: If there can be duplicate attribute values then you can COUNT(DISTINCT ...) rather than just COUNT(...).

Which, for the sample data:

CREATE TABLE application (app_id, app_name) AS
SELECT 1, 'ABC' FROM DUAL UNION ALL
SELECT 2, 'XYZ' FROM DUAL;

CREATE TABLE Application_attribute (App_attr_id, app_id, attr_name, attr_value) AS
SELECT 1, 1, 'attr1', 'white' FROM DUAL UNION ALL
SELECT 2, 1, 'attr2', '12' FROM DUAL UNION ALL
SELECT 3, 1, 'attr3', '45' FROM DUAL UNION ALL
SELECT 4, 2, 'attr1', 'red' FROM DUAL UNION ALL
SELECT 5, 2, 'attr2', '12' FROM DUAL UNION ALL
SELECT 6, 2, 'attr4', '45' FROM DUAL UNION ALL
SELECT 7, 2, 'attr7', '62' FROM DUAL;

Outputs:

APP_ID	APP_NAME
1	ABC

db<>fiddle here

Answer 3

You can join tables only once by conditionally matching the values of attr_name and attr_value columns while filtering out the result only for the returning records with exactly triple attribute names such as

SELECT a.app_id, a.app_name
  FROM application a
  JOIN application_attribute aa
    ON aa.app_id = a.app_id
 WHERE DECODE( aa.attr_name, 'attr1', aa.attr_value ) = 'white' -- you can make these literals parametric by prefixing with : or & such as &p_attr1 and enter 'white' whenever prompted
    OR DECODE( aa.attr_name, 'attr2', aa.attr_value ) = '12'
    OR DECODE( aa.attr_name, 'attr3', aa.attr_value ) = '45'  
 GROUP BY a.app_id, a.app_name
HAVING COUNT(DISTINCT aa.attr_name)=3  

Demo

Answer 4

If we re-write your query using joins there will be the 3 variables that you need to change in the WHERE. This will make it much easier to modify them.

Select a.* 
from application a
left join application-attribute at1 on a.app_id = at1.app_id and at1.attr_name = 'attr1'
left join application_attribute at2 on a.app_id = at2.app_id and at2.attr_name = 'attr2'
left join application_attribute at3 on a.app_id = at3.app_id and at3.attr_name = 'attr3'
left join application_attribute at4 on a.app_id = at4.app_id and at4.attr_name = 'attr4'
where  at1.attr_value = 'white' 
   and at2.attr_value = '12' 
   and at3.attr_value = '45'
/*   and at4.attr_value = not needed */
;