I've been searching for a solution for my problem on Stack Overflow but I can't seem to find a solution that fits, or that I'm able to implement.

I have three tables: "Extensions", "Subscriptions", "Accounts".

Extensions table:

Subscriptions table:

Accounts table:

Here there are two extensions with ID one and two, and user with ID one is subscribed to both in the subscriptions table.

I have been successful in getting a list of the extensions the signed in user is subscribed to by doing:

SELECT 
    * 
FROM 
    codium.extensions 
JOIN 
    codium.subscriptions 
JOIN 
    codium.accounts
WHERE 
    extensions.extension_id = subscriptions.extension 
AND 
    accounts.id = subscriptions.user 
AND 
    accounts.id = :id 

Then in php define what :id is with $sql->execute(["id" => $_SESSION["user_id"]]);.

However, I have been unsuccessful in showing a list of subscriptions the user is not subscribed to. If the user is subscribed to just the first extension, show the second. If the user is subscribed to none at all, show both extensions.

If it matters, when the query is executed, I use $extensions = $sql->fetchAll();, then foreach to echo a list.

I've been at this problem for a long time, using Google and logic as my friend, but I can't seem to make it work. Either none shows, or I get the correct result in very specific circumstances, i.e. if the user is just subscribed to one extension.

Appreciate all possible help.

CodePudding user response：

You are mixing old and new styles of `JOIN, when you keep to the newer one nothing can go almost wrong.

besides test yor queries before using them in code, if you are not firm with the syntax

SELECT 
    ex.*, su.*,ac.*
FROM 
    codium.extensions ex
JOIN 
    codium.subscriptions su ON ex.extension_id = su.extension  
JOIN 
    codium.accounts ac ON ac.id = su.user 
WHERE         
    ac.id = :id 

For your problem.

This shows all ROWS from extensions which are not in subscriber and conected to the selected user

SELECT * FROM
Extensions e1
WHERE
   extension_id NOT IN (SELECT extension FROM Subscriptions  WHERE user = :id)

For marking the extension already present you can use LEFT JOIN

SELECT ei.*, IF(su.subscription_id IS NOT NULL, 1,0) as marked FROM
Extensions e1 LEFT JOIN 
   (SELECT subscription_id ,extension FROM Subscriptions  WHERE user = :id) su ON ex.extension_id = su.extension