Excluding Nested SQL Results
I have a winners vs losers table (TABLE 1) for example.
+----+--------+-------+
| ID | Winner | Loser |
+----+--------+-------+
| 1 | 2 | 3 |
| 2 | 1 | 2 |
+----+--------+-------+
In the most recent game between point 1 and point 2, 1 (ID 2) won. In this example, I'll name these "Current Winner" and "Current Loser".
I am trying to build a query that works with our findings from past results.
eg. if 2> 3 and 1> 2. Then I need to write the value for 1> 3
In the query I am collecting, there are several alleged losers against the current winner.
An ideal query returns an array of losers, which I can loop through and write to the table as output. In this case, "3".
The table will be updated to:
+----+--------+-------+
| ID | Winner | Loser |
+----+--------+-------+
| 1 | 1 | 2 |
| 2 | 2 | 3 |
| 3 | 1 | 3 |
+----+--------+-------+
And if the request was run again, it will return nothing.
The process I have so far:
- See everything that happened now, loser, previously beaten (previous losers to current loser)
- Look at the chart to see if any of the previous losers to the current loser have ever played the current winner.
- Any previous loser that has should be removed
To get a list of things that a clear loser beat me up, I used:
select * from TABLE1 where winner = 2
Then, for the second marker point, I have 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 figure out how to put them together to remove the lines I don't need. Can anyone please advise me which is better and most efficient query for this example, subquery, some kind of join? The brain really fights against this.
Thank you in advance
source to share
You can do it this way by explicitly looking at certain records (match between two elements) and counting if they are null.
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
Merge tables ("from table1 ancient") will help you get the algorithm in your head.
source to share
This will give you one row for each person and competitor and the last result with that competitor: (i.e. if person 1 goes against person 2 and loses, and then goes back to that person and wins, this query will show person 1 with participant 2 WIN, and face 2 with competitor 1 LOSE). It shows the BEST result for each participant in relation 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
source to share
The query below will populate the failed losers for the last match between 1 and 2 on first run. The second time it will not insert new lines.
Originally the subquery not exists
was where id < current.id
to remove the previous losers, however, since injected games are inserted with 'future' ids (i.e. 3 in your example), if you run the query again, it will insert rows, so I changed it to where id <> current.id
that means that it also excludes "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)))
source to share