Query Help

[dengland]

Need some help with a query. Looking at my PT3 Tournament data from Cake Poker, it says that I have lost more money than I have won and deposited combined. While I can believe I lose a lot, I don't think Cake is fronting me money.

It looks like I am missing some of my results. The query I am using in pgAdmin is

Code: Select all

SELECT tourney_holdem_summary.tourney_no as "tourney_no"
FROM tourney_holdem_summary WHERE
  ((SELECT sum(val_finish)
  FROM tourney_holdem_results thr WHERE
    thr.id_tourney = tourney_holdem_summary.id_tourney) = 0)
  AND tourney_holdem_summary.id_site in (1200)
  AND tourney_holdem_summary.amt_buyin != 0
ORDER BY tourney_no DESC;

It returns results the same results as what is displayed on the "PT3 Holdem Tournament Results" screen. Looking at a recent tournament result that PT3 has, I can see what the problem is. It has data for 4th place entered, and I went out in 3rd. Data show 0, 0, 0, then 4-10 beside the player names. sum(val_finish) is non-zero, but the value for my id is zero.

I don't really understand SQL, but I am trying.

Seem like I need to effectively replace

Code: Select all

((SELECT sum(val_finish)
  FROM tourney_holdem_results thr WHERE
    thr.id_tourney = tourney_holdem_summary.id_tourney) = 0)

With something like

Code: Select all

tourney_holdem_results.id_player=243164 AND tourney_holdem_results.val_finish=0 

If I browse the tourney_holdem_results I can see there are 13 entries for me that have no results. I can create a query that shows tourney_holdem_summary.tourney_no and tourney_holdem_summary.id_tourney side by side, but that seem like cheating. Any help wth formating the query correctly would be great.

Thanks

[WhiteRider]

I'm not sure what you mean by "cheating".. If you have a query which does what you want, what is the problem?

I assume you know, but just in case - you can enter your missing results manually via the Tournaments > Enter Results window.

[dengland]

Thanks for the response.

Cheating comment was relative to I could browse the tourney_holdem_results table, filter id_player for value equal to 24364, and sort by val_finish and look for values equal to zero. I would then have a list of id_tourney that I could go manually find in the tourney_holdem_summary table to get the Cake tournament number. I am only missing 13 results, so doing that manually would not be that big of a deal.

I would rather figure out how to make the proper SQL query of the Postgres database so it is quick and repeatable. I am sure this is going to happen again.

Code: Select all

SELECT tourney_holdem_results.id_tourney
  FROM tourney_holdem_results WHERE
  tourney_holdem_results.id_player in (243164)
  AND tourney_holdem_results.val_finish=0

The above returns the 13 id_tourney numbers (internal PT3 tournament numbers). I need to make the next step and convert that to tourney_holdem_summary.tourney_no (Cake tournament numbers).

Once I have the Cake Tournament numbers, I can then enter missing results manually via the Tournaments > Enter Results window as you stated.

[kraada]

If you start PT3 with the Logging Enabled link and open the Enter Results window you can check the "Only show tournaments needing results" box and then read the log file and you'll see the query used there (it's called PokerTracker.log and is a text file).

But to convert an id_tourney to a tourney_no run this query:

SELECT tourney_no from tourney_holdem_summary where id_tourney = X;
you can also do: where id_tourney in (X, Y, Z)

or even: where id_tourney in (Select id_tourney from ...)

[dengland]

Thanks for the help guys. Problem solved.

I got what I needed with this

Code: Select all

SELECT tourney_holdem_summary.tourney_no as "tourney_no"
FROM tourney_holdem_summary WHERE
 tourney_holdem_summary.id_tourney in (SELECT tourney_holdem_results.id_tourney
  FROM tourney_holdem_results WHERE
  tourney_holdem_results.id_player in (243164)
  AND tourney_holdem_results.val_finish=0 )

It returns the tournament numbers in the database that need my attention.