Selecting from multiple tables where one is a mapping table

ChiefGoFor · #1 (**permalink**) 09-07-09, 16:55

** SOLVED **

I have two tables: jos_k2_items AND jos_k2_tags_xref

The items table contains articles that belong in my CMS (content management system). The tags_xref table maps the tags to the articles.

I am trying to search for articles based on having multiple tags assigned to them. In the example SQL below, I want articles that are tagged with tags, 3, 1, and 2. The way it is written now, it will show articles tagged with tags, 3, 1, or 2.

Thank you in advance. If I am missing anything please do not hesitate to ask.

Code:

SELECT DISTINCT(i.id)
FROM `jos_k2_items` i
INNER JOIN `jos_k2_tags_xref` tx
ON i.id=tx.itemID
WHERE tx.tagID IN (3,1,2)

CREATE TABLE `jos_k2_tags_xref` (
`id` int(11) NOT NULL auto_increment,
`tagID` int(11) NOT NULL,
`itemID` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `tagID` (`tagID`,`itemID`)
) ENGINE=MyISAM;

CREATE TABLE `jos_k2_items` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL,
`catid` int(11) NOT NULL,
`published` smallint(6) NOT NULL default '0',
`introtext` text NOT NULL,
`fulltext` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

** jos_k2_items table trimmed down to keep this cleaner.

ChiefGoFor · #2 (**permalink**) 09-08-09, 17:01

The solution involves a subquery.

Code:

select id
from (SELECT i.id, count(*) as rowCount FROM `jos_k2_items` i INNER JOIN `jos_k2_tags_xref` tx ON i.id=tx.itemID WHERE tx.tagID IN (3, 1, 2)
group by i.id) j where rowCount = 3;

Thanks to Jason and his friend Dan, this will no longer plague me. Hopefully, it will solve someone else's plague as well.

r937 · #3 (**permalink**) 09-08-09, 23:48

Quote:

Originally Posted by ChiefGoFor

The solution involves a subquery.

it doesn't have to

Code:

SELECT id
  FROM jos_k2_tags_xref AS tx
INNER 
  JOIN jos_k2_items AS i
    ON i.id = tx.itemID
 WHERE tx.tagID IN (3, 1, 2)
GROUP 
    BY id
HAVING COUNT(*) = 3

ChiefGoFor · #4 (**permalink**) 09-10-09, 00:17

@r937

Right you are! I have implemented the non-subquery method and it is working nicely. Thank you!

Internet Services

Web Development

Digital Marketing

Consumer Tech