I have a select query with a subquery in it on table a:
select UserName, Name, anniversaryDate, RegionCode
from a where UserName in
(
select Username from
(
select Username, max(timeUsed)
from log_table where role='Cou'
group by Username order by max(timeUsed) desc limit 10
) a
)
which works fine, now I have another table called b, on that table I only have 3 columns:
UserName, PName and feedback
which have each UserName multiple times and I want to join to my first query the count of each UserName I got from the first query.
for eaxmple:
SELECT COUNT(UserName) FROM b where UserName='Admin' group by UserName
How do I join the results from the second query to the first one?
Tables:
log_table :
id - Int, AI, PK
Username - varchar
Action - Int
timeUsed - Datetime
role - varchat
a:
UserName - varchar, PK
Name - varchar
anniversaryDate - Datetime
RegionCode - Int
b:
UserName - varchar
PName - varchar
feedback - varchar
Based on your table structure the query below might add the necessary column you are looking for:
select users.UserName, Name, anniversaryDate, RegionCode,
Coalesce(count(b.UserName),0) as cnt
from users
left outer join (select Username, max(timeUsed)
from log_table where role='Cou'
group by Username order by max(timeUsed) desc limit 10
) a using (UserName)
left outer join fedbacks b on (a.UserName = b.userName)
group by users.UserName;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments