SQL query for whole hand played.

[fjutoslaw]

Hi,
I need SQL query to get whole hand played: form preflop to showdown with players involved, cards and bets... As i was unable to find DB schema for HoldemMenager and digging in data base without it is so time consuming and leads me nowhere... i thought maybe some one here can help me.

I would realy appreciate any help in this subject.
Thanks a lot!

[redlotus]

Hi,
I need SQL query to get whole hand played: form preflop to showdown with players involved, cards and bets... As i was unable to find DB schema for HoldemMenager and digging in data base without it is so time consuming and leads me nowhere... i thought maybe some one here can help me.

I would realy appreciate any help in this subject.
Thanks a lot!

The short answer:
SELECT handhistory FROM handhistories
or
SELECT standardizedhandhistory FROM handhistories

Unfortunately, this will list every single hand you've ever played. Unless you happen to know the pokerhand_id of the hand(s) you are looking for, you'll still be searching through thousands of hands. If you could let me know what type of hands you are looking for, I can try to create a WHERE clause that'll help you find the hand(s).

-red

[fjutoslaw]

I need only HU hands from various stakes. I also need to specify VPIP of any of the players. something like:
SELECT handhistory from handhistories where nuber_of_players=2 and stake=1$/2$ and playerVPIP>70

I know it might be a ebig query but even if you give me any part of it or any clue it would be awesome
and you must know that optimization of thise query is not a primary think for me.
Thank you any way for what you already gave me!

[redlotus]

I need only HU hands from various stakes. I also need to specify VPIP of any of the players. something like:
SELECT handhistory from handhistories where nuber_of_players=2 and stake=1$/2$ and playerVPIP>70

I know it might be a ebig query but even if you give me any part of it or any clue it would be awesome
and you must know that optimization of thise query is not a primary think for me.
Thank you any way for what you already gave me!

I think that is all doable if you have a high enough version of PostgreSQL. Is yours 8.4 or higher?

I might be able to still do it even if you don't, but it will take a bit more time.

BTW, does HU mean heads-up to the flop or heads-up table?

-red

[fjutoslaw]

I have a 8.3 version.
Only HU table - 2 players on the table.
Thanks.

[redlotus]

Alight. I think that I have it worked out.

SELECT handhistory, standardizedhandhistory
FROM handhistories
JOIN pokerhands using (pokerhand_id)
JOIN gametypes using (gametype_id)
RIGHT OUTER JOIN playerhandscashkeycolumns using (pokerhand_id)
WHERE pokerhands.numberofplayers=2
AND bigblind=200
AND player_id in (SELECT player_id
FROM playerhandscashkeycolumns
GROUP BY player_id
HAVING AVG(case didvpip when true then 100::real else 0 end)>=70)

For anyone wanting to modify this a little, I've bolded the significant qualifiers:
pokerhands.numberofplayers is the total number of players that were dealt into the hand, so the query, as is, will include hands where only two people were dealt in on 6-max and full ring tables.
bigblind is the size of the big blind in cents ($2 bb = 200 cents)
the last number (set at 70 above) determines the vp$ip that you want to filter. Obviously, you could also change it to less than a certain number, if you want.

Anyway, give it a try and let me know if you run into problems.

-red

[fjutoslaw]

This is exactly what i was looking for. Thank you veeeery much!