postgresql - perform true on joined tables -

-

in postgresql 8.4.13 have 2 tables representing card games (each 3 players) , score results of games. players identified id column , games gid:

# \d pref_games                                     table "public.pref_games"  column |            type             |                        modifiers --------+-----------------------------+----------------------------------------------------------  gid    | integer                     | not null default nextval('pref_games_gid_seq'::regclass)  rounds | integer                     | not null  stamp  | timestamp without time zone | default now() indexes:     "pref_games_pkey" primary key, btree (gid) referenced by:     table "pref_scores" constraint "pref_scores_gid_fkey" foreign key (gid) references pref_games(gid) on delete cascade  # \d pref_scores          table "public.pref_scores"  column  |         type          | modifiers ---------+-----------------------+-----------  id      | character varying(32) | not null  gid     | integer               | not null  money   | integer               | not null  last_ip | inet                  |  quit    | boolean               | indexes:     "pref_scores_pkey" primary key, btree (id, gid)     "pref_scores_gid_index" btree (gid) foreign-key constraints:     "pref_scores_gid_fkey" foreign key (gid) references pref_games(gid) on delete cascade     "pref_scores_id_fkey" foreign key (id) references pref_users(id) on delete cascade

i'm trying find out if player id='ok261593357402' has ever played player id='mr3689735717800138910' - can allow them grade each other @ web site.

i think need call perform true on inner join - correct please?

so i'm trying:

# select * pref_games g inner join pref_scores s on (s.gid=g.gid) s.id='ok261593357402';    gid   | rounds |           stamp            |       id       |   gid   | money |    last_ip     | quit ---------+--------+----------------------------+----------------+---------+-------+----------------+------  2124241 |     20 | 2013-05-01 12:26:54.052086 | ok261593357402 | 2124241 |    28 | 93.232.91.105  | f  2125575 |     17 | 2013-05-01 16:53:21.090822 | ok261593357402 | 2125575 |    32 | 93.232.91.105  | f  2125881 |     20 | 2013-05-01 17:47:26.15633  | ok261593357402 | 2125881 |   -31 | 93.232.91.105  | f  2126242 |      0 | 2013-05-01 18:41:06.769132 | ok261593357402 | 2126242 |     0 | 93.232.91.105  | f  2126244 |      0 | 2013-05-01 18:41:12.495192 | ok261593357402 | 2126244 |     0 | 93.232.91.105  | t  2126259 |      0 | 2013-05-01 18:42:39.974518 | ok261593357402 | 2126259 |     0 | 93.232.91.105  | t  2126613 |     33 | 2013-05-01 19:27:11.88462  | ok261593357402 | 2126613 |  -132 | 93.232.91.105  | f  2126813 |      0 | 2013-05-01 19:57:05.23061  | ok261593357402 | 2126813 |     0 | 93.232.91.105  | t  2127299 |     20 | 2013-05-01 20:36:42.021133 | ok261593357402 | 2127299 |   136 | 93.232.91.105  | f  2127821 |      0 | 2013-05-01 21:33:32.168757 | ok261593357402 | 2127821 |     0 | 93.232.91.105  | f  2127830 |      0 | 2013-05-01 21:34:47.694645 | ok261593357402 | 2127830 |     0 | 93.232.91.105  | t  2128012 |     21 | 2013-05-01 22:04:03.850061 | ok261593357402 | 2128012 |    55 | 93.232.91.105  | f  2129551 |     13 | 2013-05-02 10:08:37.348426 | ok261593357402 | 2129551 |   -32 | 79.250.39.175  | f  2129818 |     13 | 2013-05-02 11:21:50.998484 | ok261593357402 | 2129818 |    71 | 79.250.39.175  | f  2130467 |     11 | 2013-05-02 13:55:00.034698 | ok261593357402 | 2130467 |   -79 | 79.250.39.175  | f  2130470 |      0 | 2013-05-02 13:55:41.298932 | ok261593357402 | 2130470 |     0 | 79.250.39.175  | f  2130476 |      0 | 2013-05-02 13:56:22.359713 | ok261593357402 | 2130476 |     0 | 79.250.39.175  | f .....

but see id='ok261593357402' above, i.e. expecting see game partners too, not delivered.

i've tried:

# select * pref_games g left outer join pref_scores s on (s.gid=g.gid) s.id='ok261593357402';    gid   | rounds |           stamp            |       id       |   gid   | money |    last_ip     | quit ---------+--------+----------------------------+----------------+---------+-------+----------------+------  2124241 |     20 | 2013-05-01 12:26:54.052086 | ok261593357402 | 2124241 |    28 | 93.232.91.105  | f  2125575 |     17 | 2013-05-01 16:53:21.090822 | ok261593357402 | 2125575 |    32 | 93.232.91.105  | f  2125881 |     20 | 2013-05-01 17:47:26.15633  | ok261593357402 | 2125881 |   -31 | 93.232.91.105  | f  2126242 |      0 | 2013-05-01 18:41:06.769132 | ok261593357402 | 2126242 |     0 | 93.232.91.105  | f  2126244 |      0 | 2013-05-01 18:41:12.495192 | ok261593357402 | 2126244 |     0 | 93.232.91.105  | t  2126259 |      0 | 2013-05-01 18:42:39.974518 | ok261593357402 | 2126259 |     0 | 93.232.91.105  | t  2126613 |     33 | 2013-05-01 19:27:11.88462  | ok261593357402 | 2126613 |  -132 | 93.232.91.105  | f  2126813 |      0 | 2013-05-01 19:57:05.23061  | ok261593357402 | 2126813 |     0 | 93.232.91.105  | t ...

unfortunately same result...

should maybe make series of selects combined exists in instead?

i'm trying find out if player id='ok261593357402' has ever played player id='mr3689735717800138910'

it's shorter / faster / simpler / more elegant use if exists ... in plpgsql perform trailing if found ...:

if exists (    select 1      pref_scores p1    join   pref_scores p2 using (gid)     p1.id = 'ok261593357402'    ,    p2.id = 'mr3689735717800138910'    )    -- stuff end if;

you don't need table pref_games @ answer question.


Comments

Post a Comment

Popular posts from this blog

Perl - how to grep a block of text from a file -

-
it can in xml or text format. how in general grep block of text in perl? <track type="ws"> <range> <rangestart>0</rangestart> <rangeend>146.912</rangeend> <locationindex>0</locationindex> <propertyindex>0</propertyindex> </range> </track> <track type="ps" id="1"> <range> <rangestart>0</rangestart> <rangeend>146.912</rangeend> <locationindex>1</locationindex> <propertyindex>1</propertyindex> </range> </track> i want grep type="ps" , till </range> . one solution open file, read line line , match block. open(fh, "file.txt"); foreach $line (<fh>) { if ($line =~ m/type="cc"(.*?)<\/range>/) { print $1; } } but there more optimal solution without reading file line l
Read more

delphi - How to remove all the grips on a coolbar if I have several coolbands? -

-
Image
if set mycoolbar.fixedorder true,only grip on first band hidden. well,if use delphi 7 create vcl forms application,then put coolbar on , create 3 coolbans hold other controls,only grip , top coolbands can hidden setting mycoolbar.fixedorder:=true. i've uploaded picture make things clear. you got fixedorder property wrong. fixed property not allow bands rearranged if set true . setting property of coolbar true keep user changing bands order @ runtime, user can still move , resize bands. can give advice can do, actual solution problem, well, have wait answer. my advice use 3 coolbars in row , setting "fixedorder" property true , bandborderstyle bsnone . way grip hidden on of them. about property, it's not bug of ide, actual preference of property.
Read more

javascript - Animating array of divs; only the final element is modified -

-
this baffling me @ moment. i'm experienced programmer looking throw bit of surprise fiancee on countdown page our wedding. desired effect simulated confetti falling behind main page. setup follows: i have array of divs in javascript. each div absolutely positioned on page. have interval set updates every 50 or milliseconds. on each tick, run loop on array of divs, , calculations , update positions. problem problem having, however, though can see divs created, stored array, , modified (in case, each has slight randomized rotation) during initialization phase, once tick update position starts, div stored in whatever last slot of array gets position updated. tried hard-coding each div index testing purposes, strictly last element update position. upon printing current "left" , "top" style values each div before , after calculations shows value has been changed, no visible change noticeable. here's basic code: javascript var container;
Read more