Hi,

We have one situation. We have table something like this:

name value property_id
x y 1
x1 y1 1
x3 y3 1

Now i need to fetch the property_id and i have the values of name and value pairs

i know x=y,x1=y1,x3=y3.
One option is using self join..is there any other option for doing this?

could you repeat the question please

i don't understand why x=y

I mean to say

i know these values
name 'x' has value 'y'
name 'x1' has value 'y1' and so on.
I need to query for multiple conditions.

which multiple conditions do you need to query for?

maybe it would make more sense if you talked about your actual problem instead of faking it with x's and y's

Ok..
The condition is in this way

Table is : ex_property
It has following properties:

name ='p' , value='071'
name='hf' , value='000'
name='main', value='4'
name='second',value='5'

Now when we say

"select property_id from ex_property where name='p' and value='071'"
It returns list of ids say (100,200,800,900,120)

and when we say
"select property_id from ex_property where name='hf' and value='000'
It returns list of ids say (100,800,765)

and so for other conditions.

In result we want the intersection of all these four conditions.
I hope you get the condition.

by the way, the design you have there is called entity-attribute-value, or EAV, and it is notoriously difficult to work with

for instance, see

• Dave's guide to the EAV
• OTLT and EAV: the two big design mistakes all beginners make

Thanks :-)
yes i will read these articles..but its leagcy db so need to do designing.

But only one thing is that , i want the and condition for two conditions.
Or will return to me more results.

WHERE ( name='p' AND value='072' )
or ( name='hf' AND value='000' )

here first condition matches it will return all ids.But its not applicable.
I want to have the "and" condition of two conditions.

you need to test what i gave you, to understand that it ~is~ the correct solution

I tried that..but seems to be results varying somewhat.

If i try some query like this

" select property_id from property where property_id in (
select property_id from property where (name in('main','main1') AND value in ('4','4'))
intersect
select property_id from property where (name in ('p','p1') and value in ('084','072'))
intersect
select property_id from property where (name in ('second','second1')) AND (value in('5','51')))"

Is there any better option for this query?
Do this query possess any performance issue?

oh

well, i've been writing SQL for over 20 years and as far as i know, the query i gave you is the solution you asked for

if you want to play around with INTERSECTs, be my guest

I tried it but it gives different results. Actually in that table
few other properties are there which have the same property_id.
Say for example:

property_id name value
12 p 1
12 hf 1
12 main 2
12 second 3
12 third 4
12 fourth 5

I think having count(*) = 2 does depend on this right?
I am not using all the properties which are having the property_id as 12.
I am using few properties to find out the property_id.
Does it matters over here?

not exactly

the COUNT(*)=2 HAVING condition makes sure that a property_id is returned by the query only if it has both of the name/value conditions in the WHERE clause