Fairly complex question about joining tables

SELECT DISTINCT
 (di.id)
FROM directoryitem di,
 location l,
 contactinfo ci,
 directoryitemtags dit
WHERE di.contactInfoId = ci.id
   AND l.id = ci.locationId
   AND dit.directoryItemId = di.id
   AND dit.tagId in (373, 123, 456)  --this section can be made dynamic with a variable
   AND l.countryId = 225
ORDER BY di.businessName ASC

Guys thanks for the prompt answers.

Unfortunately both suggestions will show me all directory items that have at least one of the tags, not both. (And I didn't know the IN() syntax so at least I learned something!)

For example, I have a listing that I've tagged with the "Hotel" and the "Premium" tags (and some other tags too). There are other directory items tagged with the Hotel tag which have not been tagged with the Premium tag.

I need to ONLY show items that have been tagged with at least BOTH and assume that these items will have other tags too.

I'll post an example with made up stuff. Here's a pretend directory item list:

ITEM: Coke. TAGS: Beverage, Sparkling, Brown
ITEM: Water. TAGS: Beverage, Clear
ITEM: Sprite. TAGS: Beverage, Sparkling, Clear

If I filter using the tags Beverage, and Sparkling I should only get Coke, and Sprite. If I use the suggestions you sent me I get all three.

Any ideas?

Something like this perhaps:

SELECT DISTINCT
  di.id
FROM directoryitem di
  INNER JOIN location l On di.contactInfoId = ci.id
  INNER JOIN contactinfo ci ON l.id = ci.locationId
WHERE
       EXISTS (
            SELECT      1
            FROM      directoryitemtags
            WHERE      directoryItemId = di.id
                      AND dit.tagId IN (342, 373)
            )
    AND l.countryId = 225
ORDER BY
      di.businessName

You can build your select statement dynamically inserting whatever number of 'ands' you need...

SELECT DISTINCT
(di.id)
FROM directoryitem di,
location l,
contactinfo ci,
directoryitemtags dit
WHERE di.contactInfoId = ci.id
  AND l.id = ci.locationId
  AND dit.directoryItemId = di.id
  AND (dit.tagId =373 and dit.tagId=123 and dit.tagId=456)  --this is can go on depending on how many tags you have.  
  AND l.countryId = 225
ORDER BY di.businessName ASC

Put all of the above into a variable and then execute the variable.  

Does that make sense?  

robertg34,

Think about it: How do you think the following logic could possibly ever be true?
AND (dit.tagId=373 and dit.tagId=123 and dit.tagId=456)

Unfortunately an integer value is a scalar (finite) value, it cannot contain multiple values.

In case you had not noticed this same point was made here http:#a25109217 by Badotz

robertg34, how I'm building the query is not the issue here. I am actually building it dynamically. My problem is that I'm not getting the results I expect.

acperkins, I just tried your suggestion. I'm getting the exact same results as Badotz's suggestion which was changing the ANDs for OR  :(

More than willing to try some other ideas... acperkins, I think you're on the right track....

>> I just tried your suggestion.<<
And you fixed the syntax error?  Otherwise I have no idea how you could have got any results.

>>acperkins, I think you're on the right track....<<
Ok, then why don't we focus on the problem, let's see if this is any closer:

SELECT      DISTINCT
        di.id
FROM    directoryitem di
        INNER JOIN ( SELECT directoryItemId
                     FROM   directoryitemtags
                     WHERE  tagId IN ( 342, 373 )
                     GROUP BY
                                          directoryItemId
                     HAVING
                                          COUNT(*) = 2
                   ) dit ON di.id = dit.directoryItemId
ORDER BY di.BusinessName

acperkins,

You are right that was silly of me..let's try this instead:

SELECT DISTINCT
(di.id)
FROM directoryitem di,
location l,
contactinfo ci,
directoryitemtags dit
WHERE di.contactInfoId = ci.id
  AND l.id = ci.locationId
  AND dit.directoryItemId = di.id
  AND dit.tagId in (373, 123, 456)
  AND l.countryId = 225
having count(dit.tagID) = 3
ORDER BY di.businessName ASC

This might work as well:

SELECT DISTINCT
(di.id)
FROM directoryitem di,
location l,
contactinfo ci,
directoryitemtags dit
WHERE di.contactInfoId = ci.id
  AND l.id = ci.locationId
  AND dit.directoryItemId = di.id
  AND dit.tagId in (373, 123, 456)
  AND l.countryId = 225
  and 3 =
(select count(directoryitemtags.directoryItemID) from directoryitem, directoryitemtags
where directoryitem.directoryItemId = directoryitemtags.id and
 directoryitemtags.tadId in (373, 123, 456) )
ORDER BY di.businessName ASC
 

and a third option:

SELECT DISTINCT
(di.id)
FROM directoryitem di inner join
location l on
di.locationId = l.id
inner join  
contactinfo ci on
l.id = ci.id
inner join
directoryitemtags dit on
di.id = dit.directoryItemId
WHERE  dit.tagId in (373, 123, 456)
AND l.countryId = 225
having count(dit.directoryItemID) = 3

I'm having too much fun with this...I'll stop until I hear something back....I think I like this option best, passing in the tag count.  Subselects can get slow not to mention complicated.  

Hope one of these helps

acperkin, True I had to tweak your query a little bit to make it work. No big deal though.
On the other hand, the last one you suggested works quite well and returns what I need. We need to add the location info and that's it! Not sure where to add this, as all my attempts resulted in whackness all over.
Would you mind explaining a little bit what it does? I've never used a whole SELECT statement inside an INNER JOIN before and I've also never used HAVING so I'm a little bit lost.

robertg34, I tried all your examples but could only make one of them work (but it returned no results when it should have returned a couple thousand). Thank you very much for your time, though. I really appreciate it!