MySQL: Querying a many to many relationship setup

[Legacy_Kato -]

               

Hi everyone,

 

I'm finishing a custom post 40 levels system, it is DB driven and I must build a query in the following context, simplified for the sake of clarity:

 

 

CREATE TABLE feats_g (
`id` smallint(4) unsigned NOT NULL,
`name` varchar(64) NOT NULL 
PRIMARY KEY (`id`))
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE ft_req (
`ft_id` smallint(4) unsigned NOT NULL,
`ft_req` smallint(4) unsigned NOT NULL,
PRIMARY KEY (`ft_id`,`ft_req`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TEMPORARY TABLE pcfeats
(id SMALLINT(4) UNSIGNED NOT NULL) ENGINE = MEMORY

Table pcfeats is a temp table, created and filled dynamically(i.e. in code) with all the feats the user already has.

Table feats_g contains all the feats

Table ft_req is the link table

 

I need the query to retrieve all feats from table feats_g which meet the conditions:

- Don't exist in table pcfeats
- Have their feat requirements, defined in link table ft_req, present in table pcfeats.(skill and class checks are performed somewhere else)

 

 

As an example(the feat numbers are fictive):

 

Table feats_g

id
0
5
20

Table ft_req
ft_id ft_req
20    5
20    0

Table pcfeats
id
5

 

 

The query would return 0, as we can see that player already has feat 5. Since feat 20 requires both feat 5 and 0, and 0 is not contained in pcfeats, feat 20 will not be returned.

 

Believe it or not, no one has yet found the solution on the DevShed, a forum of SQL experts where I have a few friends. Anyway, thanks for any insight!

 

Kato

 

 

 

               
               

               
            

[Legacy_Tarot Redhand]

               

I may not be able to help with that problem but one thing I do remember from my time at college (many, many, ... don't ask, years ago) that wherever possible you should always try to find a simpler relationship than many to many, if at all possible. If you can do that it should also simplify your query.

 

TR

               
               

               
            

[Legacy_Kato -]

               

Ya, true, yet sometimes it's unfortunately impossible to go without a many to many relationship.

               
               

               
            

[Legacy_Kato -]

               

In case anyone wants to know, here is the solution. It requires a "normal" table instead of a temp one but it is fast enough anyway.

 

SELECT feats_g.id AS featID, feats_g.name AS featNAME, pcf1.id AS hasID, NA.NAid AS NotAvailble
FROM feats_g
LEFT JOIN pcfeats AS pcf1 ON pcf1.id = feats_g.id
LEFT JOIN (
SELECT ft_req.ft_id AS NAid, COUNT(ft_req.ft_req) AS needskills, COUNT(pcf2.id) AS haskills
FROM pcfeats AS pcf2
RIGHT JOIN ft_req ON ft_req.ft_req=pcf2.id
GROUP BY ft_req.ft_id
HAVING needskills != haskills
) AS NA ON NA.NAid = feats_g.id
WHERE pcf1.id IS NULL AND NA.NAid IS NULL
ORDER BY feats_g.id;

 

 

Kato