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 ?

CodePudding user response：

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

CodePudding user response：

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 */
;

CodePudding user response：

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.