Unknown column in where clause, it does exist

Question

I have the following query:

SELECT
    *
FROM
    levelsloaded,
    (
        SELECT
            *
        FROM (
            (
                SELECT
                    id,
                    globalId,
                    date,
                    serverId,
                    playerId,
                    'playerjoins' AS origin 
                FROM
                    playerjoins
                WHERE
                    playerId = 2224
                    AND date <= levelsloaded.date 
                ORDER BY
                    date DESC
            ) UNION ALL (
                SELECT
                    id,
                    globalId,
                    date,
                    serverId,
                    playerId,
                    'playerleaves' AS origin 
                FROM
                    playerleaves
                WHERE
                    playerId = 2224
                    AND date <= levelsloaded.date 
                ORDER BY
                    date DESC
            )
            ORDER BY
                date DESC
            LIMIT 1
        ) below2
    ) below

I can assure you that levelsloaded.date does exist.

Why do I keep getting the error: SQL Error (1054): Unknown column 'levelsloaded.date' in 'where clause'. I think that by the SELECT * FROM levelsloaded I already have selected everything from levelsloaded.

Answer 1

The problem is that your join doesn't know the context in which to use levelsloaded.date.

UPDATE

I haven't tested this, but it should get you pretty close. Once we get the brute force solution, I think we can make this more efficient.

SELECT serverId, MAX(date) AS level_date FROM levelsloaded AS ll GROUP BY serverId

SELECT
    ll.*,
    pj.*,
    pl.*
FROM
    levelsloaded AS ll
JOIN (
    SELECT
        server_id,
        playerId,
        MAX(`date`) AS join_date
    FROM
        playerjoins
    WHERE
        playerId = 2246
    GROUP BY
        server_id,
        playerId
) AS pj ON pj.server_id = ll.server_id
JOIN (
    SELECT
        server_id,
        playerId,
        MAX(`date`) AS leave_date
    FROM
        playerleaves
    WHERE
        playerId = 2246
    GROUP BY
        server_id,
        playerId
) AS pl ON pl.server_id = ll.server_id
WHERE
    ll.date BETWEEN pj.join_date AND pl.leave_date;

Answer 2

Use back ticks around your column names. You have some column names that are also key words. The parser may be getting confused.