koobas.hobune.stream
Use subquery result multiple times.
I have a sql code like this
SELECT *
FROM friend
WHERE
character_id_a IN (
SELECT player_character_id
FROM player_character
WHERE game_account_id IN ('1', '2')
)
AND
character_id_b IN (
SELECT player_character_id
FROM player_character
WHERE game_account_id IN ('1', '2')
)
;
Is there a way to avoid double execution of the subquery? Thank you in advance for any help.
So the usual answer is "don't do it", can you elaborate on the purpose of character_id_a and b?
both character_id_a and character_id_b seems to return the same thing!
There is a friend table with 3 column friend_id, character_id_a, character_id_b.
The character_id is integer value which is the primary key of player_character table.
Then the game_account_id from player_character table is the primary key from game_account. Game account table store the user login id, password, name, etc.
One game account can have multiple character and the friend relation store is only between character. I want to write a sql statement to get all friend record between two game account.
This question is completely unrelated to unity, even if this is in a unity game, this very clearly an SQL question. I'd suggest going to StackOverflow which is a much more general program$$anonymous$$g support site
Can you try this?
SELECT f.* FROM friend f INNER JOIN player_character c ON (f.character_id_a =c.player_character_id OR f.character_id_b =c.player_character_id ) WHERE c.game_account_id in (1,2)
Thank you for this. I try but the result end up not only getting friend record between two game account but all friend record from these two game account
and this?
SELECT
f.*
FRO$$anonymous$$
friend f
LEFT JOIN player_character pca ON f.character_id_a = pca.player_character_id
LEFT JOIN player_character pcb ON f.character_id_b = pcb.player_character_id
WHERE
(pca.game_account_id=1 AND pcb.game_account_id=2)
OR
(pca.game_account_id=2 AND pcb.game_account_id=1)
