CODEWITHSUNDEEP
mysqlsql
matt
matt

Reputation: 787

Excluding results of nested SQL query

I have a table of winners vs losers (TABLE1) e.g.

+----+--------+-------+  
| ID | Winner | Loser |  
+----+--------+-------+  
|  1 |      2 |     3 |  
|  2 |      1 |     2 |  
+----+--------+-------+

In the most recent game between Item 1 and Item 2, 1 won (ID 2). For this example, I'll refer to this as Current Winner and Current Loser.

I'm trying to build a query that works our inferences from past results.

e.g. if 2>3, and 1>2. Then I need to record a value for 1>3

The query I'm building would find multiple inferred losers against the current winner.

The ideal query would return an array of "losers", which I can loop through and record in the table as inferred results. In this case "3".

The table would be updated to:

 +----+--------+-------+
 | ID | Winner | Loser |
 +----+--------+-------+
 |  1 |      1 |     2 |
 |  2 |      2 |     3 |
 |  3 |      1 |     3 |
 +----+--------+-------+

And if the query was run again, it would return nothing.

The process I have so far is:

To get the list of things the Current Loser has beaten i use:

    select * from TABLE1 where winner = 2

Then for the second bullet point, I've got two nested queries:

    select * from TABLE1 where winner = 1 and loser = (select loser from rp_poss where winner = 2)
    select * from TABLE1 where loser = 1 and winner = (select loser from rp_poss where winner = 2)

I really can't work out how to put these together, to remove the rows I don't want. Can somebody let me know what is best, and most efficient query for this for a example, a nested query, some kind of join? Pea brain is really struggling with this.

Thanks in advance

Upvotes: 3

Views: 617

Answers (3)

FuzzyTree
FuzzyTree

Reputation: 32392

The query below will insert inferred losers for the most recent match between 1 and 2 the first time it's run. The second time it won't insert any new rows.

Initially the not exists subquery had where id < current.id to remove previous losers, however, since inferred games are inserted with 'future' ids (i.e. 3 in your example), if you ran the query again, it would reinsert the rows, so I changed it to where id <> current.id, which means it will also exclude 'future' losers.

insert into mytable (winner, loser)
select current.winner, previous.loser
from (select id, winner, loser
    from mytable where 
    (winner = 1 and loser = 2)
    or (winner = 2 and loser = 1)
    order by id desc limit 1) current
join mytable previous 
    on previous.winner = current.loser
    and previous.id < current.id
where not exists (select 1 from mytable
    where id <> current.id 
    and ((winner = current.winner and loser = previous.loser)
    or (winner = previous.loser and loser = current.winner)))

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

This will get you one row for every person and competitor, and the last result with that competitor: (ie. if person 1 goes up against person 2 and loses, and then goes up against that person again and wins, this query will show person 1 with competitor 2 WIN, and person 2 with competitor 1 LOSE). It shows the LATEST result for each competitor, relative to the person.

http://sqlfiddle.com/#!2/823d3f/6/0

select x.person,
       case when x.person <> t.winner then t.winner else t.loser end as competitor,
       case when x.person = t.winner then 'WIN' else 'LOSE' end as result
  from (select x.winner as person, max(y.id) as id
          from (select winner from table1 union select loser from table1) x
          join table1 y
            on x.winner = y.winner
            or x.winner = y.loser
         group by x.winner) x
  join table1 t
    on x.person = t.winner
    or x.person = t.loser
 where x.id = t.id

Upvotes: 0

Steve Bennett
Steve Bennett

Reputation: 126205

You can do it this way, by explicitly looking for certain records (a match between the two items) and counting to see if there are zero of them.

CURRENTLOSER and CURRENTWINNER are placeholders for variables or whatever.

select previous.loser
from table1 previous
where previous.winner=CURRENTLOSER and (
   select count(*)
   from table1 ancient
   where (ancient.winner=CURRENTWINNER and ancient.loser=previous.loser) or
         (ancient.loser=CURRENTWINNER and ancient.winner=previous.loser)
   ) = 0

Aliasing tables ("from table1 ancient") will help get the algorithm clear in your head.

Upvotes: 1

Related Questions