SQL IN() query results producing unwanted results

Just need some help to refine this query if possible.

SELECT * 
FROM 
    E_Associates INNER JOIN E_Tag_Attributes ON E_Tag_Attributes.AssociateID = E_Associates.associateID 
WHERE 
    (TagID IN (524,546)) 
    AND CONTAINS ((AboutMe,WorkingStyle,Approach,MyValues),'werwer') 
    AND IsActive = 1

Basically I have three tables...

E_associates which is a contacts table names etc...

E_Tag_Attributes which is a linking table between E_tags table and E_associates.

I want to only display users which have BOTH the TagID 524 AND 546 records in the E_Tag_Attributes

This query however is producing results which have one or both...

Very tired, hopefully its something si开发者_如何转开发mple :)

Thanks in advance.

---

The SQL IN() clause will combine by OR and not by AND.

(...) WHERE A IN ( 1, 2, 3 )

is equivalent to

(...) WHERE ( A = 1 OR A = 2 OR A = 3 )

In this case you will have to join the same table twice and move the TagID clause in the join criteria:

SELECT * FROM E_Associates EA
  INNER JOIN E_Tag_Attributes J1 ON J1.AssociateID = EA.associateID AND TagId = 524
  INNER JOIN E_Tag_Attributes J2 ON J2.AssociateID = EA.associateID AND TagId = 526
WHERE CONTAINS ((AboutMe,WorkingStyle,Approach,MyValues),'werwer') AND IsActive = 1

I am not sure if the WHERE clause will work this way. If the columns are defined in the E_Tag_Attributes tables you may get 'ambiguous' column problems. If this is the case just put the clauses in the join criteria and put a J1. or J2. where needed.

---

Assuming you want all associates who have tags 524 and 546:

SELECT * FROM E_Associates 
INNER JOIN E_Tag_Attributes e1
  ON e1.AssociateID = E_Associates.associateID 
INNER JOIN E_Tag_Attributes e2
  ON e2.AssociateID = E_Associates.associateID 
WHERE (e1.TagID IN (524)) AND CONTAINS ((AboutMe,WorkingStyle,Approach,MyValues),'werwer') 
AND (e2.TagID IN (546)) AND CONTAINS ((AboutMe,WorkingStyle,Approach,MyValues),'werwer') 
AND IsActive = 1

---

Using the IN() clause specified that it should look for records which have any one of the specified values. So it will do what you described.

If you want to find only those where there is a related record for both the specified values, you will need to do it in the joins.

You can add the TagId field to the ON clause of the join, just the same as in a WHERE clause; just because it's in the ON clause, it doesn't have to specify a field directly linking to the main table. However in this case, using IN() here will have the same effect as it would in the WHERE clause, so we need to go further...

In order to find both of the values linked to each individual E_Associates record , you will need to join the E_Tag_Attributes table twice:

SELECT * FROM E_Associates
  INNER JOIN E_Tag_Attributes ta1 ON ta1.AssociateID = E_Associates.associateID AND ta1.TagId = 524
  INNER JOIN E_Tag_Attributes ta2 ON ta2.AssociateID = E_Associates.associateID AND ta2.TagId = 526
WHERE CONTAINS ((AboutMe,WorkingStyle,Approach,MyValues),'werwer') AND IsActive = 1

Note that I've had to add aliases (ta1 and ta2) for the two E_Tag_Attributes joins, to prevent ambiguities in the query since the table name is the same for both.

Another option you may want to consider is to query using IN() as you already are, but to use GROUP BY AssociateID, and add a WHERE clause such that COUNT(TagId)=2. This will filter the results so that only those which have two tags in the result set will be included.