Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Question Unanswered: Selecting from multiple tables where one is a mapping table

    ** 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.
    Last edited by ChiefGoFor; 09-08-09 at 18:02.

  2. #2
    Join Date
    Sep 2009
    Posts
    3

    Thumbs up

    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2009
    Posts
    3
    @r937

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •