CODEWITHSUNDEEP
phpsqlselectmedoo
Xiduzo
Xiduzo

Reputation: 1311

sql select all users from table X which are not in comination on table Y

I'm fairly new to sql and am trying to get data from table X when the user is not in table Y with the combination of player id and world id AND the player access is 2.

Let me explain a little furter:

Table X (user table)

+-----------+----------+------------+
| uid       | access   |  more data |
+-----------+----------+------------+
| 1         | 2        |    ....    |
| 2         | 1        |    ....    |
| 3         | 2        |    ....    |
+-----------+----------+------------+

Table Y (worlds)

+-----------+-----------+
| userUuid  | worldUuid |
+-----------+-----------+
| 1         | 1         |
| 2         | 2         |
| 3         | 2         |
+-----------+-----------+

When I want to get all users which I can still add to world 1 I want to get the user info from user 3.

User 1 already is in world 1, user 2 does not have access level 2 and user 3 isn't in world 1 yet and does have access level 2.

I'm using medoo and this is my statement at the moment:

$database->select("User", [
    "[>]UserInWorld" => ["uid" => "userUid"]
], [
    "uid",
    "displayname",
    "surname",
    "email"
], [
    "AND" => [
        "worldUuid[!]" => $worldUuid,
        "access" => 2
    ]
]);

The worldUuid will be the world I want to get user to add for.

When use the ->debug() the query looks like this:

SELECT "uid","displayname","surname","email" 
FROM "User" 
LEFT JOIN "UserInWorld" ON "User"."uid" = "UserInWorld"."userUid" 
WHERE "worldUuid" != '4dafb8c0-57234ff2-03eb-af7f7a5e' 
AND "access" = 2

EDIT: I posted a sollution using medoo below

Upvotes: 0

Views: 181

Answers (3)

Xiduzo
Xiduzo

Reputation: 1311

After a good night sleep I figured out how to do this using the medoo class

$database->select("User", [
    "[>]UserInWorld" => ["uid" => "userUid"]
], [
    "uid",
    "displayname",
    "surname",
    "email"
], [
    "AND" => [
        "OR" => [
            "worldUuid[!]" => [$worldUuid],
            "worldUuid" => NULL
        ],
        "access" => 2
    ],
    "GROUP" => "uid"
]);

Whereby the $worldUuid the world is I want to select users for.

This will make the following sql statement:

SELECT "uid","displayname","surname","email" FROM "User" 
LEFT JOIN "UserInWorld" ON "User"."uid" = "UserInWorld"."userUid" 
WHERE ("worldUuid" NOT IN ('1') OR "worldUuid" IS NULL) 
AND "access" = 2
GROUP BY "uid"

This will select all (unique) user who do not have a world already OR are in the world I'm getting users for AND they have access level 2

Upvotes: 0

Aidan Kane
Aidan Kane

Reputation: 4006

If I understand you correctly, you should be able to do something like this:

SELECT
    uid,
    displayname,
    surname,
    email 
FROM
    User
    LEFT JOIN UserInWorld ON User.uid = UserInWorld.userUid AND worldUuid = 1
    INNER JOIN (
        SELECT DISTINCT
            userUid
        from
            UserInWorld
        WHERE
            worldUuid != 1
    ) AS InOtherWorld ON InOtherWorld.userUid = User.uid
WHERE
    access = 2
    AND UserInWorld.userUid IS NULL

The left join will connect people in the world where possible and then UserInWorld.userUid IS NULL will effectively strip it down to those that aren't in the world.

Upvotes: 1

Lorenzo Barbagli
Lorenzo Barbagli

Reputation: 1281

you said:

am trying to get data from table X when the user is not in table Y with the combination of player id and world id AND the player access is 2

If I've understood it should be:

select * from X where X.access = 2 and X.uid not in (
    select Y.userUuid from Y
)

Upvotes: 0

Related Questions