Hi doesn't mean to be rude but i'm new to mysql
Table : info
Column : id,squad_1,squad_2,squad_12_result,squad_3,squad_4,squad_34_result
id squad_1 squad_2 squad_12_result squad_3 squad_4 squad_34_result --- ------- ------- --------------- ------- ------- --------------- 10 a b win g l win 9 a c win g m lose 8 a d draw g n win 7 a e lose g o win 6 a f win g p draw 5 a g win g q lose 4 a h win g r win 3 a i draw g s win 2 a j lose g t lose 1 a k win g u draw
what i want to achieve is latest 5 rows of 'squad_12_result' column match with entire rows of 'squad_34_result'
which mean the output result will be like
id squad_1 squad_2 squad_12_result | id squad_3 squad_4 squad_34_result --- ------- ------- --------------- | --- ------- ------- --------------- 10 a b win | 8 g n win 9 a c win | 7 g o win 8 a d draw | 6 g p draw 7 a e lose | 5 g q lose 6 a f win | 4 g r win
is there any way to achieve it? any advice are welcome
i seems to be able to match the result from both column by running this query
SELECT *
FROM `info`
WHERE squad_12_result
LIKE squad_34_result
which only return
id squad_1 squad_2 squad_12_result squad_3 squad_4 squad_34_result --- ------- ------- --------------- ------- ------- --------------- 10 a b win g l win 4 a h win g r win 2 a j lose g t lose
is there any work around to match the "squad_12_result"
id squad_1 squad_2 squad_12_result --- ------- ------- --------------- 10 a b win 9 a c win 8 a d draw 7 a e lose 6 a f win
with "squad_34_result" ?
id squad_3 squad_4 squad_34_result --- ------- ------- --------------- 8 g n win 7 g o win 6 g p draw 5 g q lose 4 g r win
basically it's compare the latest "5" rows of "squad_12_result" with entire rows of "squad_34_result" as you can see it's matching the result column
the ideal output will be something like this
id squad_1 squad_2 squad_12_result | id squad_3 squad_4 squad_34_result --- ------- ------- --------------- | --- ------- ------- --------------- 10 a b win | 8 g n win 9 a c win | 7 g o win 8 a d draw | 6 g p draw 7 a e lose | 5 g q lose 6 a f win | 4 g r win