CODEWITHSUNDEEP
mysqlsql
NyseX
NyseX

Reputation: 24

SQL: Join value from another table

I have 1 table called accounts and another one called level_points

Basically the idea is to determine what is the minimum amount of points you need to be X level.

Account Structure

id, name.. etc. points

Level_Points Structure

level, points

Values in here such as

(1, 5)
(2, 10)
(3, 15)

I'm able to calculate the level using this query

SELECT `level` 
FROM `level_points` 
WHERE `points` <= (SELECT `points` 
                   FROM `accounts` 
                   WHERE `id` = 'x') 
ORDER BY `level` 
DESC LIMIT 1

My problem is that now i'm trying to join the tables to get something like this (For every user in the accounts table)

Result:

For user 1: `id`, `name` etc... `points`, `level`
For user 2: `id`, `name` etc... `points`, `level`
For user 3: `id`, `name` etc... `points`, `level`

I'm not exactly sure how to do this using joins and I can't seem to find an answer here that helps me here.

Upvotes: 0

Views: 3043

Answers (3)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

I would change Level_Points table thus:

ALTER TABLE Level_Points
ADD max_points INT NOT NULL;

ALTER TABLE Level_Points
ADD CONSTRAINT CK_LevelPoints_VerifyPoints CHECK(points < max_points);

In this cases, points column will store the minimum num. of points and max_points columns will store the maximum num. of points required for current level. Next, I have to be sure that this level are continuous, they aren't overlapping and there is no gaps (but these restrictions are beyond the purpose of this answer: see this article wrote by Alexander Kuznetsov). Also, some indexes are needed:

CREATE UNIQUE INDEX IUN_LevelPoints_points
ON Level_Points(points);

CREATE UNIQUE INDEX IUN_LevelPoints_points_maxpoints
ON Level_Points(points, max_points); -- This index is useful because the final query needs both columns: points and max_points; in SQL Server, max_points column can be covered column: ... ON Level_Points(points) INCLUDE (max_points), then the first index (IUN_LevelPoints_points) is no more needed

Some sample rows stored into this table:

INSERT INTO Level_Points ("level", "points", "max_points") VALUES (0, 0,  5); -- level 0 for players which have less than 5 points
INSERT INTO Level_Points ("level", "points", "max_points") VALUES (1, 5,  10);
INSERT INTO Level_Points ("level", "points", "max_points") VALUES (2, 10, 15);
INSERT INTO Level_Points ("level", "points", "max_points") VALUES (3, 15, 100);
INSERT INTO Level_Points ("level", "points", "max_points") VALUES (4, 100,2147483647); -- 2147483647 is the maximum value for a INT in SQL Server

In this cases, the final query is just a simple left/inner join:

SELECT ..., lvl.level
FROM accounts AS acc LEFT JOIN Level_Points AS lvl 
ON acc.points >= lvl.points 
AND acc.points < lvl.max_points

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

Try something like this:

SELECT ac.id,
    ac.name,
    ac.points,
    le.level
FROM account ac,
    level le
WHERE ac.id = 'x'
    AND le.level= (
        SELECT level
        FROM level_points
        WHERE points <= (
                SELECT points
                FROM accounts
                WHERE id = 'x'
                )
        ORDER BY level DESC LIMIT 1
        );

To get it for all players you can do:

SELECT ac.id,
    ac.name,
    ac.points,
    le.level
FROM accounts ac,
    level_points le
WHERE le.level= (
        SELECT level
        FROM level_points
        WHERE points <= (
                SELECT points
                FROM accounts ac2
                WHERE ac2.id = ac.id
                )
        ORDER BY level DESC LIMIT 1
        );

Check this fiddle to see it working

Upvotes: 1

Vaibs_Cool
Vaibs_Cool

Reputation: 6156

Try This ..

       select column1 from table1 
       inner join table2 on table1.column = table2.column
       where table2.column=0

Have a look at this

http://www.w3schools.com/sql/sql_join.asp

Upvotes: 0

Related Questions