CODEWITHSUNDEEP
mysqlselectjoin
ndp
ndp

Reputation: 893

Optimize query selecting elements from 3 tables mysql

The database is as follows:

Classes       Challenges         Class Challenges
id             id                  id
               title               class_id
                                   challenge_id

In order to get all the challenges of a specific class I use the following

SELECT 
    DISTINCT class_challenges.challenge_id, 
    challenges.title
FROM class_challenges 
LEFT JOIN challenges 
    ON class_challenges.challenge_id = challenges.id
WHERE class_challenges.class_id = :class_id
ORDER BY challenge_id

How can I do the same for all the challenges that do not belong to a specific class? So far I use: 

SELECT 
    DISTINCT challenges.id,
    challenges.title 
FROM 
    challenges,
    class_challenges
WHERE challenges.id NOT IN(
                            SELECT 
                                DISTINCT class_challenges.challenge_id
                            FROM class_challenges
                            LEFT JOIN challenges 
                                ON class_challenges.challenge_id = challenges.id
                            WHERE class_challenges.class_id = :class_id
                            ORDER BY challenge_id
                        );

which I think can be written better. (maybe using a double join?)

So, how can this be optimized (if it can?)

Upvotes: 0

Views: 88

Answers (3)

bonCodigo
bonCodigo

Reputation: 14361

Without seeing your expected results it's a bit vague for me to answer, but anyway here is a code to try out. Call me visual ;) Please comment after you have tried out the query.

The code list down 'challenges for each class'. You may use a variable to filter out the data for a specific class id.

SQLFIDDLE DEMO

SELECT DISTINCT a.id, 
group_concat(b.challenge_id) as challengeIs,
group_concat(c.title) as Titles
FROM Classes a
LEFT JOIN 
class_challenges b
ON a.id = b.class_id
LEFT JOIN challenges c
ON b.challenge_id = c.id
group by a.id
ORDER BY a.id;

Results:

ID      CHALLENGEIS     TITLES
100     11,15       a,c
200     15          b
300     11,15       a,c
400     (null)      (null)
500     15          b

Challenges that do not belong to a specific class

Just noticed that I have missed out to add this portion of the query.

Query:

-- challenge that doesn't belong to a class

SELECT DISTINCT c.id,
c.title, group_concat(a.id) as class
FROM challenges c
LEFT JOIN 
class_challenges b
ON b.challenge_id = c.id
LEFT JOIN Classes a
ON a.id = b.class_id
GROUP BY c.id
HAVING class is null
ORDER BY c.id;

Results:

ID  TITLE   CLASS
18  c   (null)

Upvotes: 1

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Try this query

SELECT
    t.id,
    t.title,
    t.CCID
FROM
(
    SELECT      
        challenges.id,
        challenges.title,
        class_challenges.id as CCID                 
    FROM 
        challenges
    LEFT JOIN class_challenges 
        ON class_challenges.challenge_id = challenges.id
) as t  
WHERE t.CCID IS NULL

Upvotes: 1

lc.
lc.

Reputation: 116498

How about this, using an anti-join:

SELECT challenges.id, challenges.title
FROM challenges
LEFT JOIN class_challenges ON class_challenges.challenge_id = challenges.id
    AND class_challenges.class_id = :class_id
WHERE class_challenges.id IS NULL

Upvotes: 0

Related Questions