General question

[wool0135]

Hey guys, I have a general question for everyone:

Anyone know how to turn off the function that automatically turns words into statements? For example, I'm using retrosheet in SQLyog and I can't use the table named "events" because it automatically turns the word events into a statement like "Select"

I'm trying to practice JOIN statements as well, so not having access to the events table makes it a bit more difficult

[mattdennewitz]

quote reserved words like "event" or "order" or "select" with a backtick ` or (depending on your platform) a double quote.

e.g., select `event` from table_name;

best,
-m

[Ken Woolums]

Thanks to both of you. Both methods work. Here's another question (as I'm practicing my JOIN statements).

So I ran a query to give me the last names and game ID's for games where Boston was the road team and the game was played on a Wednesday. Obviously, without setting a year window, this produces a LOT of games. 

The problem I'm running into is getting the same result listed multiple times (in this case, four times). I should remember, but how do you limit the results so that the Game ID and Last name values only appear once?

[Ken Woolums]

This is the code for what I have, BTW:

"SELECT games.game_id,rosters.last_name_tx FROM games

JOIN rosters ON rosters.player_ID = games.home_start_pit_id

WHERE games.game_dy = "WEDNESDAY" AND games.AWAY_TEAM_ID = "BOS""

[Matt Hunter]

I think you want to add:

"GROUP BY game_id, rosters.last_name_tx"

to the end. Someone else can correct me if I'm wrong though.

[Ken Woolums]

That appears to have worked. Well, I now have a list of pitchers Boston has faced on the road in Wednesday games over the past however many years retrosheet includes.

Sweet. Useless, but sweet! Learning SQL is fun

[Ken Woolums]

Fun fact from my useless query:

The Red Sox faced Jim Palmer 8 times in Wednesday road games.

[KiNG KoNG]

GROUP BY should give you the correct result, but it doesn't seem like the correct approach.  It seems like you might have some bad data.  I don't think joining the starting pitcher to the game should produce 4 identical results unless there are either:

A: 4 pitchers with the same player_ID

B: 4 identical game records, or

C: 2 of each.

I may be missing something here, but I'd check that the data didn't get dumped into your tables more than once.

[Ken Woolums]

I thought it was a tad suspicious as well. Not positive how to fix it though.  Results seemed correct when I used group by.

[Ken Woolums]

Another general question for you guys. So now I'm in the "BDB" and I'm doing random queries that people propose to me (basically trivia questions). 

What I was wondering is how you can write a query that will add up all of the years in a player's career. For example, someone asked me to query for a career with 450 or more doubles and 500 or more stolen bases.

Thanks guys. Learning the ropes is frustrating

[Ken Woolums]

For the record, here is what I have that doesn't work:

SELECT master.nameFirst, master.nameLast, SUM(SB), SUM(2B), yearid

FROM MASTER

JOIN batting ON batting.playerID = master.playerID

WHERE SB>=500 AND 2B >= 450

[Alexander Dancho]

You want GROUP BY and HAVING. Here's a really helpful blog post on the two:

http://database-programmer.blogspot.com/2008/04/group-by-having-sum-avg-and-count.html

(Sorry for not getting more in depth, kind of in a hurry.)

[Ken Woolums]

Max got it, guys. Working on fixing my issue within the JOIN syntax, then I'll post it.

[Ken Woolums]

This is how you do it with JOIN:

SELECT master.nameFirst, master.playerid, master.nameLast, SUM(SB), SUM(2B)

FROM MASTER

JOIN batting ON batting.playerID = master.playerID

GROUP BY master.playerID

HAVING SUM(SB) >= 500 AND SUM(2B) >= 450

This returns the original question asked of me, which was players with at least 450 doubles and 500 stolen bases.

On Wed, May 22, 2013 at 4:19 PM, wool0135 <wool...@umn.edu> wrote:

[Colin Wyers]

You can also do:

SELECT g.game_id, r.last_name_tx FROM games g

JOIN rosters r ON r.player_ID = g.home_start_pit_id

WHERE g.game_dy = "WEDNESDAY" AND g.AWAY_TEAM_ID = "BOS"

[jonath...@gmail.com]

I ran this query (returning all the columns) and it looks like the roster table has a record for each year. If a pitcher played for 4 years, 4 rows will be returned. The GROUP BY that was suggested works well. Another option would be the 'ID' table may be better since there is only one record for each player.

SELECT games.game_id, id.last FROM games

JOIN id ON id.ID = games.home_start_pit_id

WHERE games.game_dy = "WEDNESDAY" AND games.AWAY_TEAM_ID = "BOS";

When I run into what I think is a join issue, I like to look at the data to find out what is different. That usually identifies a column that needs to be in the join. Although in this case extracting the year from the game_id, casting it to a date and then joining it with the year column in the roster table is kind of a pain.

Coming up with odd questions that can be answered with a query is fun!