query (?) problem

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

query (?) problem

Postby pasita » Mon Feb 14, 2011 7:05 am

I have 4 notes on each player. Why do I get the following output where only 3 notes are shown for some players (the hand amounts are also messed in that case)?
Code: Select all
select id_player, count(id_hand) as hands, substr(notes,0,3) from  notes N , player P natural join holdem_hand_player_statistics S
where N.id_x = S.id_player and S.id_hand>0
group by id_player,notes
order by id_player


1;6;"T"
1;6;"B"
1;6;"F"
1;6;"-"
2;6;"T"
2;12;"-"
2;6;"F"
3;12;"-"
3;6;"F"
3;6;"T"
4;8;"-"
4;4;"F"
4;4;"T"
5;1883;"T"
5;1883;"B"
5;1883;"F"
5;1883;"W"
pasita
 
Posts: 605
Joined: Tue May 06, 2008 4:05 pm

Re: query (?) problem

Postby kraada » Mon Feb 14, 2011 10:03 am

Do you have aliases set up in this database?
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: query (?) problem

Postby pasita » Mon Feb 14, 2011 10:04 am

None.
pasita
 
Posts: 605
Joined: Tue May 06, 2008 4:05 pm

Re: query (?) problem

Postby pasita » Mon Feb 14, 2011 10:09 am

I may just have got it. Seems that if I have 4 different notes, it works out. But if I have two similar notes (namely "-" ) on one player, it gets grouped. How do I prevent that when I'm forced to group by note also?
pasita
 
Posts: 605
Joined: Tue May 06, 2008 4:05 pm

Re: query (?) problem

Postby kraada » Mon Feb 14, 2011 10:19 am

Why would you ever want two identical notes on the same player?
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: query (?) problem

Postby pasita » Mon Feb 14, 2011 10:34 am

Well,
Jan 1st: Fish
Jan 2nd: Yes, still a Fish
Jan 3rd: Maybe improving???
Jan 4rd: Yes, still a Fish

;)

In reality, I'm autogenerating those notes. But this is fine now as I understood it's because of the grouping. It's not like I'm new to sql.... just that it's like two decades since my last JOINs :)
pasita
 
Posts: 605
Joined: Tue May 06, 2008 4:05 pm

Re: query (?) problem

Postby kraada » Mon Feb 14, 2011 11:22 am

Understood :)
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