SQLite - Match records data-CodePudding

I have a database with a list of game related trades in it, and I'm trying to identify the trades that match. Let me explain better.

I have the following records:

UserID  GameID  PlatformID  RecivingGameID  RecivingGamePlatformID  PostID
1111    18      167         41              43                      2312451124
2222    41      43          18              167                     1276949826
3333    41      43          18              21                      6798639876
4444    41      43          90              167                     4587938698

In this table there is only 1 match, between post 2312451124 and 1276949826. This is because both users have the game that the other user wants for a specific platform.

So if I am the user 1111, the only match I have to see is with the user 2222 and vice versa. Users 3333 and 4444 have no matches in the database.

How can I identify the trades that match?

Here's what I tried:

SELECT * FROM Posts WHERE UserID != 1111 AND (RecivingGameID IN (
    SELECT DISTINCT GameID FROM Posts) AND GameID IN (
        SELECT DISTINCT RecivingGameID FROM Posts)) AND (RecivingGamePlatformID IN (
            SELECT DISTINCT PlatformID FROM Posts) AND PlatformID IN (
                SELECT DISTINCT RecivingGamePlatformID FROM Posts))

SELECT * FROM Posts WHERE UserID != 1111 AND RecivingGameID IN (
    SELECT DISTINCT GameID FROM Posts) AND GameID IN (
        SELECT DISTINCT RecivingGameID FROM Posts) AND RecivingGamePlatformID IN (
            SELECT DISTINCT PlatformID FROM Posts) AND PlatformID IN (
                SELECT DISTINCT RecivingGamePlatformID FROM Posts)

CodePudding user response：


WITH
    Posts(UserID, GameID, PlatformID, RecivingGameID, RecivingGamePlatformID, PostID) AS (
        VALUES
            (1111, 18, 167, 41, 43, 2312451124),
            (2222, 41, 43, 18, 167, 1276949826),
            (3333, 41, 43, 18, 21, 6798639876),
            (4444, 41, 43, 90, 167, 4587938698)
    ),
    matches AS (
        SELECT DISTINCT
            src.UserID AS UserID, src.PostID AS PostID,
            dst.UserID AS RecivingUserID, dst.PostID AS RecivingPostID
        FROM Posts AS src, Posts AS dst
        WHERE src.GameID = dst.RecivingGameID
          AND dst.GameID = src.RecivingGameID
          AND src.PlatformID = dst.RecivingGamePlatformID
          AND dst.PlatformID = src.RecivingGamePlatformID
          AND src.UserID < dst.UserID
    )
SELECT * FROM matches;

CodePudding user response：

Use a self join:

SELECT p1.*
FROM Posts p1 INNER JOIN Posts p2
ON p2.UserID <> p1.UserID 
AND (p1.GameID, p1.PlatformID) = (p2.RecivingGameID, p2.RecivingGamePlatformID) 
AND (p2.GameID, p2.PlatformID) = (p1.RecivingGameID, p1.RecivingGamePlatformID) 
WHERE p2.UserID = 1111;

See the demo.