/    Sign up×
Community /Pin to ProfileBookmark

MySQL Joined Search

Copy linkTweet thisAlerts:
Nov 05.2021

Good Evening,

I’m trying to find matches in my database based on players. My project is a online game tracking tool. I have a database full of games played. It has a unique ID called gameID.

I also have a gamePlayers table with members ID and what side they were on.

I’m trying to find games where multiple players have played together

[code]
SELECT * FROM `games` g JOIN `gamePlayers` gp on gp.gameID = g.gameID
WHERE gp.memberID IN (?)
[/code]

If I pass in 4 members should I only get games where all 4 played the same game or will it return a game where one is found?

What can I do to only get full matches?

Regards

to post a comment
PHP

3 Comments(s) ↴

Copy linkTweet thisAlerts:
@NogDogNov 05.2021 — Maybe...
[code=sql]
select
g.gameID,
json_arrayagg(gp.memberID) as member_ids
from games g
inner join gamePlayers gp on gp.gameID = g.gameID
where bp.memberID IN(1, 2, 3, 4)
group by g.gameID
[/code]
member_ids should then populate with a JSON array of matched member IDs. However, it might have duplicates, so you might want to load it into a PHP array via json_decode() and then apply array_unique() to it -- or wait for someone to suggest a better approach. :)
Copy linkTweet thisAlerts:
@singaporeDudeauthorNov 05.2021 — Thank you my friend.

It appears the query is finding any games where the joined table has any matches with a memberID's in the IN clause.

Here is a visual example of what I'm trying to do.

**Games**

gameID | gameDate | gameLevel

1 | '2015-09-24 20:00:00.000000', 5

2 | '2015-09-25 20:00:00.000000', 6

3 | '2015-09-29 20:00:00.000000', 2

4 | '2015-09-30 20:00:00.000000', 6

**gamePlayers**

gameID | player ID

1 | 14

2 | 33

2| 23

2 |14

3| 27

4 | 33

So I want to find all games where member 33 and 14 have played which is game 2 only.
Copy linkTweet thisAlerts:
@singaporeDudeauthorNov 06.2021 — Hello Again,

Just updating future readers - I used this solution and it appears to work

https://stackoverflow.com/questions/11636061/matching-all-values-in-in-clause
×

Success!

Help @singaporeDude spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.8,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...