[SOLVED] Help with query - varying multiple joins

[darcuss]

Hi

 

I am really having a difficult time figuring this out, so please forgive my first post here being one asking for help.

 

It goes like this:

 

I have three tables. One of items, one of categories and one of tokens. Each item falls under a category and has one or more tokens. A token is a descriptive characteristic of the item.

 

What I need to do is query the three tables by multiple tokens and have returned each category with the incidences of items within that have all of the multiple tokens.

 

For example

Item 1 is of category 3 and uniquely of all the other items has three tokens: 'red', 'round' and small. Querying would return:

 

catName----| - count   

--------------|---------

Category 1  | 0

Category 2  | 0

Category 3  | 1

Category 4  | 0

...

 

If for example I had searched only for the token 'red', I might have had more categories returned due to the lesser specificity of the characteristics represented by the tokens. Varying numbers of tokens may be searched for.

 

Hopefully that's clear enough.

 

I had tried this query:

SELECT c.id
     , c.name
     , COALESCE(matches.cnt, 0) AS count
  FROM cats AS c
LEFT OUTER
  JOIN ( SELECT COUNT(*) as cnt
              , id
           FROM items as i
         INNER
           JOIN itemToks as it
             ON it.itemId = i.id
	  WHERE it.tok in ('green', 'round', 'small')
          GROUP
             BY id ) AS matches
    ON c.id = matches.id

But using an IN statement means that *any* of the tokens matched is sufficient for a join, rather than *all* of them.

 

Here is an idea of the tables

 

items

+------+-------+--------------------------------+

| id | name | des | itemCat |

+------+-------+--------------------------------+

| 1 | item 1 | item desc 1 | 2 |

| 2 | item 2 | item desc 2 | 3 |

| 3 | item 3 | item desc 3 | 7 |

| 4 | item 4 | item desc 4 | 3 |

| 5 | item 5 | item desc 5 | 5 |

| 6 | item 6 | item desc 6 | 6 |

| 7 | item 7 | item desc 7 | 1 |

+------+--------------+-----------------|---------+

 

 

cats

+----+---------------+---------------+

| id | name | des |

+----+---------------+---------------+

| 1 | cat 1 | Cat desc 1 |

| 2 | cat 2 | Cat desc 2 |

| 3 | cat 3 | Cat desc 3 |

| 4 | cat 4 | Cat desc 4 |

| 5 | cat 5 | Cat desc 5 |

| 6 | cat 6 | Cat desc 6 |

| 7 | cat 7 | Cat desc 7 |

+----+---------------+--------------+

 

 

itemToks

+-------+---------------+

| itemId | tok |

+-------+---------------+

| 1 | green |

| 1 | round |

| 1 | large |

| 2 | small |

| 2 | yellow |

| 3 | small |

| 3 | blue |

| 4 | small |

| 4 | red |

| 5 | square |

| 5 | orange |

| 5 | large |

| 6 | pink |

| 6 | triangular |

| 6 | medium |

| 6 | yellow |

| 7 | round |

| 7 | purple |

| 7 | massive |

| 8 | brown |

| 8 | tiny |

+-------+---------------+

 

A dump of the database:

 

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `cats` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`des` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

insert into `cats` values('1','cat1','Cat desc 1'),
('2','cat2','Cat desc 2'),
('3','cat3','Cat desc 3'),
('4','cat4','Cat desc 4'),
('5','cat5','Cat desc 5'),
('6','cat6','Cat desc 6'),
('7','cat7','Cat desc 7');

CREATE TABLE `items` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`des` varchar(100) NOT NULL,
`catId` int(10) unsigned default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

insert into `items` values('1','item 1','item desc 1','2'),
('2','item 2','item desc 2','3'),
('3','item 3','item desc 3','7'),
('4','item 4','item desc 4','3'),
('5','item 5','item desc 5','5'),
('6','item 6','item desc 6','6'),
('7','item 7','item desc 7','1');

CREATE TABLE `itemToks` (
`itemId` int(10) unsigned NOT NULL,
`tok` varchar(100) NOT NULL,
PRIMARY KEY (`itemId`,`tok`)
) ENGINE=innoDB DEFAULT CHARSET=latin1;

insert into `itemToks` values('1','green'),
('1','large'),
('1','round'),
('2','small'),
('2','yellow'),
('3','blue'),
('3','small'),
('4','red'),
('4','small'),
('5','large'),
('5','orange'),
('5','square'),
('6','medium'),
('6','pink'),
('6','triangular'),
('6','yellow'),
('7','massive'),
('7','purple'),
('7','round'),
('8','brown'),
('8','tiny');

SET FOREIGN_KEY_CHECKS = 1; 

Again I'm sorry for turning up here on the lookout for favours but i'd really appreciate help on this. The explanation seems clear to me but i've been looking at this now for two days and can't find a solution.

 

Thanks in advance for anyone reading this sprawling post.

[Barand]

I just answered a similar problem here

 

http://www.phpfreaks.com/forums/index.php/topic,222388.msg1022320.html#msg1022320

[Barand]

try

SELECT c.name, COUNT(X.name) FROM cats c
LEFT JOIN
(
  SELECT i.`name`, i.catid, COUNT(*) as ct
  FROM items i
  INNER JOIN itemtoks t ON i.id = t.itemid AND t.tok IN ('green','round','large')
  GROUP BY i.`name`
  HAVING ct > 2
) as X
ON c.id = X.catid
GROUP BY c.name

[darcuss]

Absolutely brilliant, works perfectly. Thanks very much for your help!!!