Post reply

Sql Query Question

  • August 31, 2004 at 2:41 am

    #162458

    Its getting late and I am stumped with this question.

    I have the following scenario, I have a Person Table, a person has 0 or many properties associated with them, I need to be able to find all people that have all the following properties.

    Person Table

    PersonID (PK)

    Name

     

    Property

    PropertyID (PK)

    Name

     

    PersonProperty

    PersonID (PK)

    PropertyID (PK)

     

    What would the Sql Select statement look like where I want to find all users that have Property 2 and 6 and 9.

    Thanks

     

    Mike

  • August 31, 2004 at 3:07 am

    #521175

    select Person.Name,Property.Name

    from PersonProperty

    join Person

    on Person.PersonID = PersonProperty.PersonID

    join Property

    on Property.PropertyID = PersonProperty.PropertyID

    where Property.PropertyID =2

    AND Property.PropertyID =6

    AND Property.PropertyID =9

    Thanks,

    Ganesh

  • August 31, 2004 at 3:23 am

    #521178

    Unfortunatly that does not work, when you have the where clause being

     

    Property.PropertyID =2

    AND Property.PropertyID =6

    AND Property.PropertyID =9

  • August 31, 2004 at 3:29 am

    #521179

    Hi,

    If you are dealing with a N to N relation on table PERSON and PROPERTY ( using the inbetween table PERSONPROPERTY ) I'd rather use;

    SELECT a.name

    FROM    person a,

               personproperty b,

               property c,

    WHERE  a.Personid = b.Personid

    AND      b.Personid = c.Personid

    AND      c.Personid in (2,6,9 )

    a) First of all the IN clause in much faster as using the AND in the where-clause.

    b) Better design you databases according to ( international ) standards. The column-names have to refer to the table-name so it can be determened where the PERSONID column ( with resides in all these 3 tables ) belong(s) to.

    Person table -- per_id (PK)

                         per_name

    Property table -- pro_id (PK)

                           pro_name

    PersonProperty table -- pp_per_id (PK)

                                     pp_pro_id (PK)

    I'm a DBA for some time now and have been working in several companies but a good database design is hard to find!!!! 

  • August 31, 2004 at 4:09 am

    #521182

    Thanks for your reply, however the query would not work as the Property table does not have a PersonID column.

    If you change the query to the following it has the effect of ORing the values, I need to be able to get a list of people that have propertid 2 AND 6 AND 9

    SELECT a.name

    FROM    person a,

               personproperty b,

               property c,

    WHERE  a.Personid = b.Personid

    AND      b.Propertyid = c.Propertyid

    AND      c.Propertyid in (2,6,9 )

  • August 31, 2004 at 5:39 am

    #521192

    Mike, use the following query:

    SELECT a.name 
FROM person a 
INNER JOIN personproperty b ON a.Personid = b.Personid
INNER JOIN property c ON b.Propertyid = c.Propertyid
WHERE c.Propertyid in (2,6,9)
GROUP BY a.name
HAVING COUNT(*)=3

    (I hope that name is unique in the person table)

    You should read this interesting article about Relational division, by Joe Celko:

    http://www.dbazine.com/celko1.html

    Razvan

  • August 31, 2004 at 10:44 am

    #521258

    Thanks Razvan, works really well.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply