CODEWITHSUNDEEP
phpmysql
Staggan
Staggan

Reputation: 77

Joining two MySQL queries

I am trying to combine two queries.

The first gives me all registrations in a specific month.

SELECT player_id from player where registration_datetime like '%2013-12%';

The second query finds users that have logged in in Jan, Feb and March

SELECT player_id
                        FROM login_history
                        GROUP BY player_id
                        HAVING SUM(timestamp BETWEEN UNIX_TIMESTAMP('2014-01-01 00:00:00') AND (UNIX_TIMESTAMP('2014-02-01 00:00:00')-1)) > 0 and
                               SUM(timestamp BETWEEN UNIX_TIMESTAMP('2014-02-01 00:00:00') AND (UNIX_TIMESTAMP('2014-03-01 00:00:00')-1)) > 0 and
                               SUM(timestamp BETWEEN UNIX_TIMESTAMP('2014-03-01 00:00:00') AND (UNIX_TIMESTAMP('2014-04-01 00:00:00')-1)) > 0
                         ;

How can I find the common player_id's between the two queries without using an IN ?

Thanks

Upvotes: 1

Views: 75

Answers (3)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

you could use an UNION ALL on your 2 queries, group by player_id and having count(*) > 1

or use an inner join (I would add a distinct in the joined query)

SELECT player_id
FROM login_history
join (SELECT distinct player_id from player where registration_datetime like '%2013-12%') s
  on s.player_id = player_id                   
GROUP BY player_id
HAVING SUM(timestamp BETWEEN UNIX_TIMESTAMP('2014-01-01 00:00:00') AND (UNIX_TIMESTAMP('2014-02-01 00:00:00')-1)) > 0 and
       SUM(timestamp BETWEEN UNIX_TIMESTAMP('2014-02-01 00:00:00') AND (UNIX_TIMESTAMP('2014-03-01 00:00:00')-1)) > 0 and
       SUM(timestamp BETWEEN UNIX_TIMESTAMP('2014-03-01 00:00:00') AND (UNIX_TIMESTAMP('2014-04-01 00:00:00')-1)) > 0

Upvotes: 2

user2174835
user2174835

Reputation: 366

sqlfiddle would be nice.

But did you try joining these two tables and then grouping data?

Something like this: 

SELECT p.player_id FROM player p 
JOIN login_history lh ON p.player_id = lh.player_id
WHERE p.registration_datetime like '%2013-12%'
GROUP BY p.player_id
HAVING SUM(lh.timestamp BETWEEN UNIX_TIMESTAMP('2014-01-01 00:00:00') AND (UNIX_TIMESTAMP('2014-02-01 00:00:00')-1)) > 0 and
       SUM(lh.timestamp BETWEEN UNIX_TIMESTAMP('2014-02-01 00:00:00') AND (UNIX_TIMESTAMP('2014-03-01 00:00:00')-1)) > 0 and
       SUM(lh.timestamp BETWEEN UNIX_TIMESTAMP('2014-03-01 00:00:00') AND (UNIX_TIMESTAMP('2014-04-01 00:00:00')-1)) > 0;

Upvotes: 1

Evan Volgas
Evan Volgas

Reputation: 2911

You can use a subquery and a join to accomplish this. The first part of the query returns the player id from player. The second part subsets your login_history to only the ids you want, ones in which the user logged in between Jan and March. This table is joined with players, giving you only the ids of users who registered in Jan and logged in during Jan, Feb, and Mar

SELECT a.player_id 
FROM player AS a 
INNER JOIN (
SELECT player_id
FROM login_history
GROUP BY player_id
HAVING SUM(timestamp BETWEEN UNIX_TIMESTAMP('2014-01-01 00:00:00') AND (UNIX_TIMESTAMP('2014-02-01 00:00:00')-1)) > 0 AND
SUM(timestamp BETWEEN UNIX_TIMESTAMP('2014-02-01 00:00:00') AND (UNIX_TIMESTAMP('2014-03-01 00:00:00')-1)) > 0 AND
SUM(timestamp BETWEEN UNIX_TIMESTAMP('2014-03-01 00:00:00') AND (UNIX_TIMESTAMP('2014-04-01 00:00:00')-1)) > 0) AS b
ON a.player_id = b.player_id
WHERE a.registration_datetime like '%2013-12%'

Upvotes: 0

Related Questions