report showing tournies with notes at tny level

Forum for users that want to write their own custom queries against the PT database either via the Structured Query Language (SQL) or using the PT3 custom stats/reports interface.

Moderator: Moderators

report showing tournies with notes at tny level

Postby zubs1aa » Fri Sep 03, 2010 1:41 am

I'm trying to write a report that shows tournies where I saved a note at the tourny level (not in a hand) and having trouble.

holdem tournament tournament should be the section?

check for notes = true ?

Doing this gives way too many lines.. and it looks like it gives me a line for each hand in the tourney!
Also, it only shows an icon for the note being true - but not the note itself.

I'm guessing I have to hook into a notes file or something? all help appreciated.

THanks
zubs1aa
 
Posts: 2219
Joined: Fri Feb 08, 2008 1:52 pm

Re: report showing tournies with notes at tny level

Postby kraada » Fri Sep 03, 2010 8:42 am

Try filtering for notes.enum_type = 'T' and see if that works better.
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: report showing tournies with notes at tny level

Postby zubs1aa » Fri Sep 03, 2010 10:02 pm

kraada wrote:Try filtering for notes.enum_type = 'T' and see if that works better.


i did a lot of looking at the table structures last night. I think that will leave me with only one line per tourny which is good. However, what I really want to do is to have report list the tournies.. AND the associated notes. TO do that there needs to be some kind of lookup into the notes file?
zubs1aa
 
Posts: 2219
Joined: Fri Feb 08, 2008 1:52 pm

Re: report showing tournies with notes at tny level

Postby WhiteRider » Sat Sep 04, 2010 1:40 am

Yeah, you can't currently list the notes associated with a tournament, or any other item that can have notes (as far as I know, anyway - Kraada may be able to come up with a cunning way to access it as we learn more about the database, but I don't think so - the report will list tournaments, and since each tournament can have multiple notes that doesn't fit).
WhiteRider
Moderator
 
Posts: 54025
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: report showing tournies with notes at tny level

Postby zubs1aa » Sat Sep 04, 2010 3:35 am

WhiteRider wrote:Yeah, you can't currently list the notes associated with a tournament, or any other item that can have notes (as far as I know, anyway - Kraada may be able to come up with a cunning way to access it as we learn more about the database, but I don't think so - the report will list tournaments, and since each tournament can have multiple notes that doesn't fit).


perhaps perhaps kraada can figure out how to clean this up below and suggest the optimal solution:

1) it's pretty easy in sql - it's just a simple join - though i'd have to bone up on my sql unfortunately.1
need to join tourney_holdem_summary to notes via the tourney_holdem_summary.tourney_no to notes.id_x fields.

once joined this lets you grab the right entries. Would be trivial for instance if I dumped the tables to MS access

2) Or, it's an easy function to write -in quasi code you would need a function that does basically this:

For each tourney_holdem_summary.id
- get notes.notes where

--tourney_holdem_summary.flag_note= true <---there is a note on that tny

--notes.id_x = tourney_holdem_summary.tourney_no <-----links tourney to note file

--notes.enum_typ="THS" <--------THS flags summary level, THH flags hand level
zubs1aa
 
Posts: 2219
Joined: Fri Feb 08, 2008 1:52 pm

Re: report showing tournies with notes at tny level

Postby kraada » Sat Sep 04, 2010 9:36 am

You'd be joining on id_tourney to id_x, actually - but how to set this up in the UI for users is less than obvious.

And you also need to be very careful with the query. I just spent some time with it trying to find a workaround for you and I managed to nearly hang my computer with a bad query . . .
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY


Return to Custom Stats, Reports, and SQL [Read Only]

Who is online

Users browsing this forum: No registered users and 0 guests