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

Question

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

But from your sample seems you want the userUid with level access = 2 not in User .. ? — ScaisEdge, May 05 '16 at 20:08

Aidan Kane · Answer 1 · 2016-05-05T21:12:43.330

1

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.

edited May 05 '16 at 21:12

answered May 05 '16 at 20:08

Aidan Kane

3,856
2
25
28

Thanks for the reply, but I also want to get users which already do have connection in table Y. I just don't want to get users which already are in table Y with a specific world. (users can be added to multiple worlds) – Xiduzo May 05 '16 at 20:14
I've added another join to only include people who are already in a different world. – Aidan Kane May 05 '16 at 21:13
Thanks for your help, after a good night sleep I figured out a way to do this in medoo. WIll update my post – Xiduzo May 06 '16 at 10:41

score 0 · Answer 2 · answered May 05 '16 at 20:14

0

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
)

answered May 05 '16 at 20:14

Lorenzo Barbagli

1,241
2
16
34

They can be in table Y allready, just not in combination with worldUuid X – Xiduzo May 05 '16 at 20:16
If you mean: select all users with access = 2 AND with a corrispondence in worldUuid you just have to change Y.userUuid to Y.worldUuid in my code's answer – Lorenzo Barbagli May 05 '16 at 20:18

score 0 · Accepted Answer · answered May 06 '16 at 10:48

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

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

3 Answers3