CODEWITHSUNDEEP
mysqlsql
TheDuncan
TheDuncan

Reputation: 50

MYSQL: Select from table A, based on seen date of user in Table B

OK I have three tables RegionName(Key, Name), Players(Key, Name, Seen), and regionplayers(key, regionkey, playerkey)

there is more to each table but it isn't needed so to make this easier i have only included what is needed. I have regions, and a player can be added to a region, a region may have many players, and players can be on many regions. there are 1680 regions, 900ish players and about 3500 entries in the table linking them together.

I want to be able to find regions, where none of the players have been seen in 8+ days.

what I have atm is: 

SELECT RegionName.*, RegionPlayer.*, Players.*
FROM RegionName
JOIN RegionPlayer
    ON RegionPlayer.Regionkey= RegionName.Key
JOIN Players
    ON Players.Key = RegionPlayer.Playerkey
WHERE
    ( Seen <= (NOW() - INTERVAL 8 DAY   ) ) ) 
    AND RegionName.Perent = 'none'
    ORDER BY `RegionName`.`Name` ASC,
    Players.Seen DESC

currently I get all the regions where people who have not been seen for 8+ days, so if a region has 1 player who hasn't been seen, it is returned, but I only want regions where everyone has been away for 8+days.

Here is a sort of sampling if the data for each of the tables, this was done by me just now. because its just easier then trying to pull enough data that makes the point, and should give the results I want.

RegionName
    key Name
    1   regionone
    2   regiontwo
    3   regionthree
    4   regionfouor
    5   regionfive

Players
    Key     Name    Seen
    1       jack    2014-03-21 12:43:46
    2       joe     2014-03-26 12:43:46
    3       bob     2014-03-20 12:43:46
    4       bill    2014-03-19 12:43:46
    5       dave    2014-03-17 12:43:46
    6       tina    2014-03-28 12:43:46
    7       tony    2014-03-29 12:43:46
    8       george  2014-03-15 12:43:46
    9       sam     2014-03-18 12:43:46
    10      frank   2014-03-18 12:43:46

RegionPlayer
    key Regionkey   PlayerKey
    1   1           1
    2   1           4
    3   1           5
    4   2           1
    5   2           4
    6   2           2
    7   3           6
    8   3           1
    9   3           7
    10  4           1
    11  4           8
    12  4           7
    13  4           5
    14  5           3

So I should based on this data get back regions regionone, & regionfive.

Upvotes: 1

Views: 64

Answers (1)

Mihai
Mihai

Reputation: 26784

SELECT RegionName.*, RegionPlayer.*, Players.*
FROM RegionName
JOIN RegionPlayer
    ON RegionPlayer.Regionkey= RegionName.Key
JOIN Players
    ON Players.Key = RegionPlayer.Playerkey
WHERE RegionName.Perent = 'none'
GROUP BY RegionName.Name
HAVING 
    SUM( Seen > (NOW() - INTERVAL 8 DAY ) ) =0
    ORDER BY `RegionName`.`Name` ASC,
    Players.Seen DESC

Upvotes: 1

Related Questions