Join 2 mysql select based on text field

Question

I have 2 of the following select in Mysql:

1st choice:

(SELECT DISTINCT `Online_playerdatabase_v2`.`Player`, Online_playerdatabase_v2.First_Deposit_Date As FirstDep, TRUNCATE(Online_playerdatabase_v2.Balance,2) as Balance FROM Online_playerdatabase_v2 WHERE `Online_playerdatabase_v2`.`Player`<>'Player' ORDER BY `Online_playerdatabase_v2`.`Balance` DESC; 

2d select:

 SELECT DISTINCT(Online_customer_activity_v2.Customers) as Player, max(Online_customer_activity_v2.Date) as LastAction FROM Online_customer_activity_v2 WHERE `Online_customer_activity_v2`.`Total_Bets`>0 Group by Online_customer_activity_v2.Customers 

Exit Select 1

 Player FirstDep Balance Ray 2014-10-19 9100.00 Ramzi 2014-11-02 9.61 tareq 2014-11-06 805.00 STAN 2014-10-17 7.50 Bill 2014-03-25 68.40 karam 2014-11-16 676.50 Abdul 2014-11-13 650.00 Renaud 2014-03-12 507.00 John 2014-11-22 500.00 

Pin Selection 2

 Player LastAction John 2015-11-13 Bill 2014-12-14 Renaud 2015-03-14 Abdul 2015-11-16 Ray 2015-11-22 STAN 2015-10-29 Ramzi 2015-11-10 Tarek 2015-05-10 karam 2014-12-10 Abdul 2015-02-10 

Desired output, join on both, select that adds the following calculations: active days (FirstDep-LastAction) and Days_last_Visit (CurrentDate - Last Action)

To summarize in the following table:

 Player FirstDep Balance LastAction Active_days Days_last_Visit Ray 2014-10-19 9100.00 2015-11-22 399 1 Ramzi 2014-11-02 9.61 2015-11-10 373 13 tareq 2014-11-06 805.00 2015-05-10 185 197 STAN 2014-10-17 7.50 2015-10-29 377 25 Bill 2014-03-25 68.40 2014-12-14 264 344 karam 2014-11-16 676.50 2014-12-10 24 348 Abdul 2014-11-13 650.00 2015-02-10 89 286 Renaud 2014-03-12 507.00 2015-03-14 367 254 John 2014-11-22 500.00 2015-11-13 356 10 

Your help is much appreciated! Thanks

Answer 1

The following query should give the desired result. I will add that I joined the two tables from your intermediate queries above using the Player field. This is not a very reliable way to join, because the name cannot be unique among all the players in the table. The best way to join is to use a unique identifier of some type.

 SELECT t1.Player, t1.FirstDep, t1.Balance, t2.LastAction, DATEDIFF(t2.LastAction, t1.FirstDep) AS Active_days, DATEDIFF(NOW(), t2.LastAction) AS Days_last_Visit FROM ( SELECT DISTINCT `Online_playerdatabase_v2`.`Player`, Online_playerdatabase_v2.First_Deposit_Date AS FirstDep, TRUNCATE(Online_playerdatabase_v2.Balance,2) AS Balance FROM Online_playerdatabase_v2 WHERE `Online_playerdatabase_v2`.`Player` <> 'Player' ) t1 INNER JOIN ( SELECT DISTINCT(Online_customer_activity_v2.Customers) AS Player, MAX(Online_customer_activity_v2.Date) AS LastAction FROM Online_customer_activity_v2 WHERE `Online_customer_activity_v2`.`Total_Bets` > 0 GROUP BY Online_customer_activity_v2.Customers ) t2 ON t1.`Player` = t2.`Player` 

Answer 2

You need to join the 2 selected as subqueries in the third choice using the player’s field. The Active_days and Days_last_Visit fields can be calculated using the DateDiff () function.

 SELECT * ,DateDiff(t2.LastAction,t1.FirstDep) AS Active_days ,DateDiff(CURDATE(), t2.LastAction) AS Days_last_Visit FROM (SELECT DISTINCT `Online_playerdatabase_v2`.`Player`, Online_playerdatabase_v2.First_Deposit_Date As FirstDep, TRUNCATE(Online_playerdatabase_v2.Balance,2) as Balance FROM Online_playerdatabase_v2 WHERE `Online_playerdatabase_v2`.`Player`<>'Player' ORDER BY `Online_playerdatabase_v2`.`Balance` DESC) t1 LEFT JOIN (SELECT DISTINCT(Online_customer_activity_v2.Customers) as Player, max(Online_customer_activity_v2.Date) as LastAction FROM Online_customer_activity_v2 WHERE `Online_customer_activity_v2`.`Total_Bets`>0 Group by Online_customer_activity_v2.Customers) t2 ON t1.Player=t2.Player 

However, you must consider how you join 2 datasets. I used the left join, as the players table is likely to contain all the players, but you may want to go to the inner join or simulate a full outer join depending on your requirements and your data.