CODEWITHSUNDEEP
mysqljoin
Korenron
Korenron

Reputation: 101

mysql - get data from another tabel according to search?

I have 2 tables that use the same ID as PK , I have make a "where" question in table2 and got a resault

now I wnat to use the ID I got and see what is the Name from table 1 

table 1 (Users) is 

ID Name 
1 David
2 Guy
3 Robert 
4 Helen 

table 2 (history_Users_daily )is 

ID ADDED_Money User_Date
1   0          2019-02-07
1   1          2019-02-08
1   0          2019-02-10
2   0          2019-02-07
2   0          2019-02-13
3   5          2019-02-12
4   0          2019-02-12
4   0          2019-02-13

this is the what I did on table 2:

SELECT ID,SUM(ADDED_Money)  AS Total_Money_7_Days
from 
v7.history_Users_daily 
where ( com_id = '1' and User_DATE >= date(curdate() - 7)  )
GROUP BY ID
HAVING Total_Money_7_Days < '2'
order by Total_Money_7_Days ;

when I run this I see all the ID that have less then 2

this is the result I'm getting :

 ID Total_Money_7_Days
 1  1
 2  0
 4  0

now I want to see what is the Name of each ID according to table 1

so in the final I will get this :

ID Name Total_Money_7_Days
1  David 1
2  Guy   0
4  Helen 0

I have try all kinds of "Join"(examples I have found on google, but none of them work )

what I need to do?

Thanks ,

Upvotes: 0

Views: 21

Answers (2)

Nick
Nick

Reputation: 147206

You can just JOIN directly to the Users table:

SELECT h.ID, u.Name, SUM(h.ADDED_Money)  AS Total_Money_7_Days
FROM history_Users_daily h
JOIN Users u ON u.ID = h.ID
WHERE ( com_id = '1' AND User_DATE >= date(curdate() - 7)  )
GROUP BY h.ID, u.Name
HAVING Total_Money_7_Days < '2'
ORDER BY Total_Money_7_Days DESC

Output

ID  Name    Total_Money_7_Days
1   David   1
2   Guy     0
4   Helen   0

Demo on dbfiddle

Upvotes: 1

Harshil Doshi
Harshil Doshi

Reputation: 3592

select t2.Id, t1.Name, t2.Total_Money_7_Days
from users t1
Join (SELECT ID,SUM(ADDED_Money)  AS Total_Money_7_Days
     from 
     v7.history_Users_daily 
     where ( com_id = '1' and User_DATE >= date(curdate() - 7)  )
     GROUP BY ID
     HAVING Total_Money_7_Days < '2'
     order by Total_Money_7_Days
     ) t2
on t2.Id = t1.Id

Upvotes: 1

Related Questions