Need help with position expressions

[Usoborod]

Hi, I have a custom report with a list of players. Like this

tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND (p.player_name = 'villain1 or p.player_name = 'villain2'))

I need to specify position for that list of player how do I do that?
I also know that I can set my position (BU=0 for example) by adding outside of the first expression

and tourney_hand_player_statistics.position = 0

But it doesn't work for that list if I add same string inside the expression like that

tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND (p.player_name = 'villain1 or p.player_name = 'villain2' and tourney_hand_player_statistics.position = 9))

What I also need is to make a third player who is NOT from the list or is NOT me and is not on position = 9 or 0. How do I proceed?
My theory so far

tourney_hand_player_statistics.position = 0 and tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND (p.player_name = 'villain1' or p.player_name = 'villain2' and tourney_hand_player_statistics.position = 9)) and ((NOT me and NOT from the list) and tourney_hand_player_statistics.position = 8)

[Flag_Hippo]

Hi, I have a custom report with a list of players. Like this

tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND (p.player_name = 'villain1 or p.player_name = 'villain2'))

Bear in mind that this isn't a valid expression - it's missing the a single quote at the end of villain1.

I need to specify position for that list of player how do I do that?
I also know that I can set my position (BU=0 for example) by adding outside of the first expression

and tourney_hand_player_statistics.position = 0


But it doesn't work for that list if I add same string inside the expression like that

tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND (p.player_name = 'villain1 or p.player_name = 'villain2' and tourney_hand_player_statistics.position = 9))

This expression filter uses a subquery to filter data that's not from the active players perspective so that will not work - for more on how SELECT works see this guide. The correct expression would be:

Code: Select all

tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND (p.player_name = 'villain1' or p.player_name = 'villain2') and thps.position = 9)

What I also need is to make a third player who is NOT from the list or is NOT me and is not on position = 9 or 0. How do I proceed?
My theory so far

tourney_hand_player_statistics.position = 0 and tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND (p.player_name = 'villain1' or p.player_name = 'villain2' and tourney_hand_player_statistics.position = 9)) and ((NOT me and NOT from the list) and tourney_hand_player_statistics.position = 8)

You are using position = 8 in your theory so presumably this is for 3 handed play only? If I am understanding what you want you will need to add a separate expression filter for that:

Code: Select all

NOT tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND (p.player_name = 'villain1' or p.player_name = 'villain2') and thps.position = 8)

[Usoborod]

Thanks for help. It was for spins 3way vs reg spots, I ended up just adding a filter that 3rd player is not vpiping preflop, found it in some of your links.
It's working perfectly for hand reports but still 3rd player is leaking though if I make a player report. For some reason in the player report 3rdP player is not always folding preflop. The same copied filters, its weird.

[Flag_Hippo]

Bear in mind if the third player is in the big blind then they do not necessarily need to VPIP to see a flop because either the pot is limped or they were shortstacked and forced all-in when posting the big blind.

[Usoborod]

I excluded that by adding normal filter "position of first raiser BU".
The problem is in case of the player report I didn't check "Filter on active player" which is making so it starts to include hands when other players are "Hero" vs my list.
So I have my wr vs regs, but still I can't see their separate wr vs me.

How I can specify that player name on BB is only "ME" for example?

tourney_hand_player_statistics.position = 8 and tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND p.player_name = 'Myname')

This is valid but doesn't work.
and

tourney_hand_player_statistics.position = 8 and p.player_name = 'Myname'

is not valid
also not working

tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND p.player_name = 'Myname' and thps.position = 8)

[Flag_Hippo]

How I can specify that player name on BB is only "ME" for example?

tourney_hand_player_statistics.position = 8 and tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND p.player_name = 'Myname')

This is valid but doesn't work.
and

tourney_hand_player_statistics.position = 8 and p.player_name = 'Myname'

is not valid

You can only use tourney_hand_player_statistics.position = 8 to specify your position when you are the active player and the 'Filter on Active Player' option is turned on.

also not working

tourney_hand_player_statistics.id_hand IN (SELECT thps.id_hand FROM tourney_hand_player_statistics thps, player p WHERE thps.id_player=p.id_player AND p.player_name = 'Myname' and thps.position = 8)

This works for me - the only data I see is when the named player is in the big blind. Please specify exactly how it isn't working for you including screenshots if necessary.

[Usoborod]

Sorry I confused myself, it works, but it doesn't show what I'm looking for. Regs have inflated wr in this spot vs me (and vice versa), because even if 3rd player on SB always folds due to filter, they still donate 0.5bb.
So I need to somehow retract this -0.5bb from every pot that was played. I should probably create a custom stat like "chips won - 0.5bb"? Well, I did and it split every name a few times for different bb levels
I'll just ignore those 0.5 and a few recs in a list, anyway, this inflation happens for both sides, so it evens out eventually.