MySQL : complex query dynamic select?

Question

I searching for many times now on a complex MySQL query.

It's an achievement system.

Structure of tables is simple and form is symmetric.

The first table (achievements_base) contains the achievements reference : each achievement have : - an ID (id of the row, auto-increment & unique), - a code_id (reference of the achievement), - a rank_id (sub-category of achievement's code_id), - and the achievement name (display purposes).

The second table (achievements_user) contains the achievements obtained by the users : each achievement unlocked have : - an ID (if of the row, auto-increment & unique), - the code_id (reference of the achievement), - a rank_id (sub-category of achievement's code_id), - and the user id.

The tables syntax is :

achievements_base
id - code_id - rank_id - name
1 - 1 - 1 - foo
2 - 1 - 2 - bar
3 - 1 - 3 - foobar
4 - 1 - 4 - foofoo
5 - 2 - 1 - barbar

achievements_user
id - code_id - rank_id - user_id
1 - 1 - 1 - 1
2 - 1 - 2 - 1
3 - 2 - 1 - 1

I want to display achievements that user don't have (GROUP BY code_id)

In the table exemple above, for example, expected result is, for the user_id 1 :
3 - 1 - 3 - foobar

But I don't see how to do that ! I've already tried multiples queries.

Sorry for my English, I'm French !

Thanks !

edit, example of one of queries that I've tried :

SELECT AB.name
FROM achievements_base AB
RIGHT JOIN achievements_users AU 
ON AU.code_id = AB.code_id AND AU.rank_id = AB.rank_id
WHERE (SELECT COUNT(*) FROM achievements_users AU WHERE AU.user_id = 1) = 0
GROUP BY AB.code_id
ORDER BY AB.code_id ASC

Show us what you have tried.

Kermit
– Kermit

2012-10-31 00:23:01 +00:00
Commented Oct 31, 2012 at 0:23 — Kermit
– Kermit, Commented Oct 31, 2012 at 0:23

Adam Wenger · Accepted Answer · 2012-10-31 00:56:25Z

1

How about the following?

SELECT code_id
FROM achievements_base
WHERE code_id NOT IN
( 
   SELECT code_id
   FROM achievements_user
   WHERE user_id = 1
)
GROUP BY code_id

edited Oct 31, 2012 at 0:56

Adam Wenger

17.7k8 gold badges55 silver badges65 bronze badges

answered Oct 31, 2012 at 0:37

xelber

4,6653 gold badges30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Franck Boudraa Over a year ago

WHERE NOT IN is the good syntax, it's effective ! Big thank you ! ;)

DevlshOne Over a year ago

Check carefully here.. this will check the code_id only, not the combination of code_id and rank_id. I'm not sure if that's acceptable for your purpose. If it is, GREAT! :)

Kloar · Accepted Answer · 2012-10-31 00:36:02Z

1

SELECT ab.*
FROM achievements_base ab
LEFT OUTER JOIN achievements_user au 
ON ab.id = au.code_id
WHERE au.user_id = {userid}

Untested, and I'm just now learning joins, but an outer join is definitely what you want to go with.

answered Oct 31, 2012 at 0:36

Kloar

1,1602 gold badges9 silver badges26 bronze badges

1 Comment

Franck Boudraa Over a year ago

No sorry outer join is not the solution ;)

DevlshOne · Accepted Answer · 2012-10-31 00:49:24Z

0

What about something like this? EDIT This should do the trick..

SELECT 
    * 
FROM
    achievements_base 
WHERE id NOT IN 
    (SELECT 
        ab.id 
    FROM
        achievements_base ab 
        RIGHT JOIN achievements_user au 
            ON (
                ab.code_id = au.code_id 
                AND ab.rank_id = au.rank_id
            ) 
    WHERE au.user_id = 1)

edited Oct 31, 2012 at 0:49

answered Oct 31, 2012 at 0:37

DevlshOne

8,4671 gold badge31 silver badges38 bronze badges

Collectives™ on Stack Overflow

MySQL : complex query dynamic select?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related