Results 1 to 12 of 12

Thread: Sql Query help

  1. #1
    Join Date
    Aug 2009
    Posts
    6

    Red face Unanswered: Sql Query help

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you repeat the question please

    i don't understand why x=y
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2009
    Posts
    6
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2009
    Posts
    6
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT property_id 
      FROM ex_property 
     WHERE ( name='p' AND value='071' )
        OR ( name='hf' AND value='000' )
    GROUP
        BY property_id
    HAVING COUNT(*) = 2
    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


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2009
    Posts
    6
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SQLDBUSer123
    But only one thing is that , i want the and condition for two conditions.
    Or will return to me more results.
    you need to test what i gave you, to understand that it ~is~ the correct solution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2009
    Posts
    6
    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?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SQLDBUSer123
    I tried that..but seems to be results varying somewhat.
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2009
    Posts
    6
    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?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SQLDBUSer123
    I think having count(*) = 2 does depend on this right?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •