CODEWITHSUNDEEP
sql-serverjoin
Raj Rao
Raj Rao

Reputation: 9138

SQLServer Select that joins and returns results where multiple join records match values

Say I have 2 tables:

Person

+-------------+
| Id.   Name. |
+-------------+
| 1.    Hello |
| 2.    World |
+-------------+

PersonAttribute

+------------------------------------------------------+
| Id.   PersonId.     AttributeName   AttributeValue   |
+------------------------------------------------------+
|  1.     1.             Gender             M          |
|  2.     1.             WearsGlasses       Y          |
|  3.     1.             LikesColorGreen    N          |
|  4.     2.             Gender             F          |
|  5.     2.             WearsGlasses       N          |
|  6.     2.             LikesColorGreen    Y          |
+------------------------------------------------------+

What if I wanted to return results where I wanted to look for people that matched an arbitary set of attributes

So I have 2 questions:

how would I search for people with a query like: Gender = F and LikesColorGreen = Y

What if I wanted to make the query allow for an arbitrary set of attributes Eg: a Query table specifies

QueryAttributeName QueryValue

+----------------------------------+
|  1. LikesColorGreen           N  |
|  2. WearsGlasses              Y  |
|  3. Gender                    F  |
+----------------------------------+

The one query I have for "Gender = F and LikesColorGreen = Y" is:

select * from Person p
Join PersonAttribute pa on p.id = pa.PersonId and pa.AttributeName = Gender and AttributeValue = F
interset
select * from Person p
Join PersonAttribute pa on p.id = pa.PersonId and pa.AttributeName = LikesColorGreen and AttributeValue = Y

Is there a better query that will get this?

What is such a query called?

Upvotes: 2

Views: 37

Answers (2)

Brian Pressler
Brian Pressler

Reputation: 6713

What you are trying to implement is called a Entity-Attribute-Value Model (EAV). It can give you some flexibility in satisfying customer's unique requirements without needing to modify application code and database structure, but it also has it's drawbacks.

One thing that will enhance your performance is if you create another table for attributes so that you are using an [AttributeID] in PersonAttribute instead of the text. That will make your joins much faster and reduce the size of your table which could get pretty big depending on how many entities and attributes you add.

All of the attributes in your examples are binary values (y/n, m/f... etc). If a majority of your attributes are really bit flags, you might get more bang from storing some data in int, bigint, or varbinary fields and have a configuration table allocate which bits map to your attributes. Then do a check on a bit mask to find out if a record matches your query parameters. You could also do a mix, using bit fields and an attribute table.

Otherwise your fastest query to get all attribute matches with AND is probably just going to be your standard join with appropriate indexes:

select distinct Person.*
from Person
join PersonAttribute att1
on Person.PersonID=att1.PersonID
  and att1.attId = 1 --maps to AGE
  and att1.AttributeValue BETWEEN 30 AND 40
join PersonAttribute att2
on Person.PersonID=att2.PersonID
  and att2.attId = 1 --maps to Income
  and att2.AttributeValue < 200000
join ... etc.

Upvotes: 1

Tim3880
Tim3880

Reputation: 2583

You can try this :

 select pid from atts where
  (aname='Gender' and avalue='M') 
  or      
  (aname='WearsGlasses' and avalue='Y')
  or
  (aname='LikesColorGreen' and avalue='Y')
   group by pid having COUNT(*)=3

It's easy to expand the query to check more attributes.

Performance can be an issue but with this kind of data structure, you can't expect too much.

If you have a table to match, you can try:

select pid from atts a join att_match m 
on a.aname=m.aname and a.avalue = m.avalue
group by pid having COUNT(*)= (select count(*) from att_match);

it matches all conditions in your "match crietria" table.

Upvotes: 1

Related Questions