Jump to content
Sign in to follow this  
Epicblood

SQL Filters and Joins

Recommended Posts

Alright class, today we will be learning about SQL Joins (que moans and groans).

 

SQL Joins are something I myself only just learned about so bear with me here. 

 

Let's say you have two tables

Players:

user_id | user_name | ip_addr

----------|----------------|-----------

0         | "Epicblood"   | 127.0.0.1

1         | "iDeath"       | 72.65.12.56

2         | "someOtherFag | 85.254.23.46

 

and Kills:

user_ded | user_kill | how

-------------|------------|--------

0            | 1          | "knife"

2            | 0          | "rpg"

Now we want to find All kills involving player "Epicblood" how do this?

simple really, you use a join (Hurray!!)

First let me show you the final query:

SELECT ded_guy, user_name AS murderer, how FROM(
	SELECT user_name AS ded_guy, user_kill, how FROM (
		SELECT user_name, user_kill, how
		FROM Kills
		JOIN Players ON (
		   Kills.user_ded = Players.user_id
		)
	) AS ded_logs
) AS murder_logs
JOIN Players ON (
	user_kill = Players.user_id
)
WHERE ded_guy = "Epicblood" OR user_name = "Epicblood";
Now this is a confusing query, no?

let's break it down.

First we are doing this:

SELECT user_name, user_kill, how
FROM Kills
JOIN Players ON (
    Kills.user_ded = Players.user_id
);
This is just grabbing the row in `players` that have the `user_id` of `user_ded`, so this will return both tables more or less combined.

then we basically filter that query, to only get the parts we want.

 

SELECT user_name AS ded_guy, user_kill, how FROM (
    SELECT user_name, user_kill, how
    FROM Kills
    JOIN Players ON (
        Kills.user_ded = Players.user_id
    )
) AS ded_logs
This is saying I want to get the field `user_name` and name it `ded_guy`, I also want the fields `user_kill` and `how`.

Now we have the username of the dead player in the column `ded_guy` and the user_id of the murderer in `user_kill`, all that's left is to get the username of the murderer:

SELECT ded_guy, user_name AS murderer, how FROM(
	SELECT user_name AS ded_guy, user_kill, how FROM (
		SELECT user_name, user_kill, how
		FROM Kills
		JOIN Players ON (
		   Kills.user_ded = Players.user_id
		)
	) AS ded_logs
) AS murder_logs
JOIN Players ON (
	user_kill = Players.user_id
)
Here we are doing a JOIN, similar to the one we did earlier, but instead of using `user_ded`, we use `user_kill`. This is again going to return both tables, but we dont want all those values.

so we name `user_name` `murderer`, and grab `ded_guy` and `how`.

Lastly we want to filter it to only return kills involving `Epicblood`

SELECT ded_guy, user_name AS murderer, how FROM(
	SELECT user_name AS ded_guy, user_kill, how FROM (
		SELECT user_name, user_kill, how
		FROM Kills
		JOIN Players ON (
		   Kills.user_ded = Players.user_id
		)
	) AS ded_logs
) AS murder_logs
JOIN Players ON (
	user_kill = Players.user_id
)
WHERE ded_guy = "Epicblood" OR user_name = "Epicblood";
Which we can do with a simple WHERE clause.

This will return:

ded_guy | murderer | how

-------------|-----------|------

someOtherFag | Epicblood | rpg

Epicblood | iDeath | knife

Share this post


Link to post
Share on other sites

I'll be referring to this for a script I'm working on. Thank you!

 

Out of curiosity, is it possible to change certain aspects of the SQL?

 

For example, GM ranks are assigned numbers in the core and in the DB, can you change them to letters and get the same affect?

Share this post


Link to post
Share on other sites

I'll be referring to this for a script I'm working on. Thank you!

 

Out of curiosity, is it possible to change certain aspects of the SQL?

 

For example, GM ranks are assigned numbers in the core and in the DB, can you change them to letters and get the same affect?

yep! you can definitely do that :P

Share this post


Link to post
Share on other sites

Okay so what I understood for example if I have

 

Stats

user_id  | player_name  | player_gold

0            | GoldHacker    |  14.000

1            | RussianHacker | 32.200

2            | Farmer              | 150.000

 

and for example

 

methods

| user_hours |user_method

  5    | hacks

  10   | morehacks

  150 |  nolife

 

 

 

would something like this work?

SELECT player_name, user_hours, user_method
FROM Stats, methods

Would that return any results?

 

Take note I`m beginner in SQL and still learning

 

 

Share this post


Link to post
Share on other sites

Okay so what I understood for example if I have

 

Stats

user_id  | player_name  | player_gold

0            | GoldHacker    |  14.000

1            | RussianHacker | 32.200

2            | Farmer              | 150.000

 

and for example

 

methods

| user_hours |user_method

  5    | hacks

  10   | morehacks

  150 |  nolife

 

 

 

would something like this work?

SELECT player_name, user_hours, user_method
FROM Stats, methods
Would that return any results?

 

Take note I`m beginner in SQL and still learning

that would return something like this:

'GoldHacker', '5', 'hacks'
'RussianHacker', '5', 'hacks'
'Farmer', '5', 'hacks'
'GoldHacker', '10', 'morehacks'
'RussianHacker', '10', 'morehacks'
'Farmer', '10', 'morehacks'
'GoldHacker', '150', 'nolife'
'RussianHacker', '150', 'nolife'
'Farmer', '150', 'nolife'
if you wanna run it yourself here is the test query I used:

http://paste.epicblood.club/view/f8e868d3

Share this post


Link to post
Share on other sites

Why tho everything is multiplied?

 

 

and if we would like to see  how much gold member has made and what method I`m guessing I would need to switch instead of user_hours  add player_gold?

I`m very interested in learning SQL so I`m reading up on guides to become awesome like you :P

Share this post


Link to post
Share on other sites

Why tho everything is multiplied?

 

 

and if we would like to see  how much gold member has made and what method I`m guessing I would need to switch instead of user_hours  add player_gold?

I`m very interested in learning SQL so I`m reading up on guides to become awesome like you 23_stuck_out_tongue_closed_eyes.png

Everything is multiplied because your SQL server has no idea what the relationship between those two tables is, so for each entry it gets from Stats, it adds on every entry of methods.

What you want to do is give methods a structure like:

user_id| user_hours |user_method

0| 5    | hacks

1| 10   | morehacks

2| 150 |  nolife

 

then do a join on the user_id so it matched them correctly.

Share this post


Link to post
Share on other sites

Everything is multiplied because your SQL server has no idea what the relationship between those two tables is, so for each entry it gets from Stats, it adds on every entry of methods.

What you want to do is give methods a structure like:

user_id| user_hours |user_method

0| 5    | hacks

1| 10   | morehacks

2| 150 |  nolife

 

then do a join on the user_id so it matched them correctly.

 

Do you have skype perhaps?If so pm me your skype.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×