SQL for VLOOKUP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL for VLOOKUP

Author

Topic

crookesa
Starting Member

7 Posts

Posted - 2013-08-10 : 07:45:47

Hi,

I have 2 tables, scores and teams and have an excel spreadsheet setup to calculate results.
I'm trying to replicate it online but having trouble creating an sql statement.

scores contains fields called player and score
teams contains a username, player1, player2 and player3.

I want to display these on a html table where the player1 score is looked up from the the scores table based on the player1 value (similar to vlookup in excel) and so on for player2 and player3. I then want to total the 3 players scores and give a grand total for each username.

I have been trying to use joins but seem to be getting lost.

Any help would be appreciated.

Alan.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-10 : 09:43:07

Are there only four columns - username, player1, player2 and player3 - in teams table?

crookesa
Starting Member

7 Posts

Posted - 2013-08-10 : 09:53:01

No there's another field at the moment called made_cut but may have more in the future.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-10 : 13:01:22

The equivalent in SQL Server to VLOOKUP is named JOIN.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

crookesa
Starting Member

7 Posts

Posted - 2013-08-10 : 14:40:49

Yeah, I'm having trouble creating the SQL statement I keep getting syntax errors. I think it's because I'm trying to use mutliple joins in the join part of the statement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-10 : 14:48:00

Start with this

SELECT		*
FROM		dbo.Teams AS t
LEFT JOIN	dbo.Scores AS s1 ON s1.Player = t.Player1
LEFT JOIN	dbo.Scores AS s2 ON s2.Player = t.Player2
LEFT JOIN	dbo.Scores AS s3 ON s3.Player = t.Player3;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

crookesa
Starting Member

7 Posts

Posted - 2013-08-10 : 15:08:54

This is what I have already

select

entries.name as name,
entries.player1, player1_score,
entries.player2, player2_score,
entries.player3, player3_score,
player1_score+player2_score+player3_score as total_score

from pga_championship_2013 as entries

inner join leaderboard_A as player1_score on entries.player1 = leaderboard_A.name
inner join leaderboard_A as player2_score on entries.player2 = leaderboard_A.name
inner join leaderboard_A as player3_score on entries.player3 = leaderboard_A.name

where leaderboard_A.tournament_name = "PGA Championship" and year = "2013"

jethrow
Starting Member

37 Posts

Posted - 2013-08-10 : 15:58:41

Here's a working example based on the info from the OP using SwePeso's approach:

declare @scores table (player varchar(20), score int)
Insert Into @scores values
	('Jake', 10),
	('Aiden', 25),
	('Mike', 32),
	('Ben', 13),
	('Josh', 27),
	('Andrew', 7);
declare @teams table (username varchar(20), player1 varchar(20), player2 varchar(20), player3 varchar(20))
Insert Into @teams values
	('David', 'Ben', 'Aiden', 'Josh'),
	('Aaron', 'Mike', 'Jake', 'Andrew');

SELECT	t.username,
	t.player1, s1.score player1_score,
	t.player2, s2.score player2_score,
	t.player3, s3.score player3_score,
	s1.score+s2.score+s3.score total_score
FROM	@teams t
	Left Join @scores s1 On t.player1=s1.player
	Left Join @scores s2 On t.player2=s2.player
	Left Join @scores s3 On t.player3=s3.player;

crookesa
Starting Member

7 Posts

Posted - 2013-08-10 : 16:20:38

Thanks for the quick reply. In my scores I have the same player listed mutiple times for different tournaments so I'm trying to add in a where clause

WHERE leaderboard_A.tournament_name = "PGA Championship"

but I'm getting an error (leaderboard_A is my @scores table)

#1054 - Unknown column 'leaderboard_A.tournament_name' in 'where clause'

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-10 : 16:31:48

Here is jethrow's example extended to use where clause

[CODE]
declare @scores table (player varchar(20), score int, tournament varchar(20))
Insert Into @scores values
('Jake', 10, 'PGA Championship'),
('Aiden', 25, 'PGA Championship'),
('Mike', 32, 'PGA Championship'),
('Ben', 13, 'PGA Championship'),
('Josh', 27, 'PGA Championship'),
('Andrew', 7, 'PGA Championship'),
('Jake', 10, 'NFL Championship'),
('Aiden', 25, 'NFL Championship');
declare @teams table (username varchar(20), player1 varchar(20), player2 varchar(20), player3 varchar(20))
Insert Into @teams values
('David', 'Ben', 'Aiden', 'Josh'),
('Aaron', 'Mike', 'Jake', 'Andrew');

SELECT t.username,
t.player1, s1.score player1_score,
t.player2, s2.score player2_score,
t.player3, s3.score player3_score,
s1.score+s2.score+s3.score total_score
FROM @teams t
Left Join @scores s1 On t.player1=s1.player
Left Join @scores s2 On t.player2=s2.player
Left Join @scores s3 On t.player3=s3.player
WHERE s1.tournament = 'PGA Championship';

[/CODE]

crookesa
Starting Member

7 Posts

Posted - 2013-08-10 : 17:25:10

That is brilliant, thanks a million, working perfect now. I'm still learning this.
Is there a function in MySQL to give me a RANK.
In excel the rank will give my the position of results and if tied for example will say 1,2,2,4,5,6,6,6,9.

Is this possible using MySQL query?

jethrow
Starting Member

37 Posts

Posted - 2013-08-10 : 17:40:42

Using SQL Server you have Rank:

declare @scores table (player varchar(20), score int, tournament varchar(20))
Insert Into @scores values
	('Jake', 10, 'PGA Championship'),
	('Aiden', 25, 'PGA Championship'),
	('Mike', 32, 'PGA Championship'),
	('Ben', 13, 'PGA Championship'),
	('Josh', 27, 'PGA Championship'),
	('Andrew', 7, 'PGA Championship'),
	('Jake', 10, 'NFL Championship'),
	('Aiden', 25, 'NFL Championship');

Select	Rank() Over(Partition by tournament Order by score Desc) [Rank], *
From	@scores;

crookesa
Starting Member

7 Posts

Posted - 2013-08-10 : 17:43:34

Is there one for MySQL? I'm trying to replicate a spreadsheet for a competition online and I have it all created in excel. Unfortunately my server only has MySQL. The ones I've found online don't facilitate what I want to do.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-10 : 21:23:04

Here is a solution
You may want to post this on MySQL forum to get a simpler solution:
[CODE]

USE TEST;

CREATE table Scores (player varchar(20), score int, tournament varchar(20));
Insert Into Scores values
('Jake', 10, 'PGA Championship'),
('Aiden', 25, 'PGA Championship'),
('Mike', 10, 'PGA Championship'),
('Ben', 13, 'PGA Championship'),
('Josh', 27, 'PGA Championship'),
('Andrew', 7, 'PGA Championship'),
('Jake', 10, 'NFL Championship'),
('Aiden', 25, 'NFL Championship');

SET @prev := NULL;
SET @curr := NULL;
SET @CurTor := NULL;
SET @PrevTor := NULL;
SET @rank := 0;
SELECT player,
@PrevTor := @CurTor,
@CurTor := a.tournament,
@rank := IF(@prevTor = @CurTor, @rank, 0),
IF(@prevTor = @CurTor, @prev := @curr, @prev := NULL),
@curr := a.score AS Score,
@rank := IF(@prev = @curr, @rank, @rank+1) AS rank
FROM Scores a
order by tournament, score;

DROP TABLE Scores;
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources