I want to get the count of addmited records by user in 2 table. for example, I have a list of users(table STF) and want to know how many product did a user create (in table PV1) and how many product did he sell (in table dpq), I want to show these data like below:
I have these 2 queries, and don't know how to show them in one table with 3 columns...
Query 1:
select staff_username, COUNT(*) as 'count 1'
from STF right join PV1 on STF.staff_username = PV1.admit_user
group by staff_username, staff_name + ' ' + staff_family
Query 2:
select trf_staff_id, COUNT(trf_staff_id)
from dpq join stf on trf_staff_id = stf.staff_username
group by trf_staff_id
You can try joining two queries like this. From limited info which you have provided it seems that staff_username is same as trf_staff_id as you have used it in join condition of second query.
SELECT
staff_username,
count_1,
count_2 FROM
(
SELECT
staff_username,
COUNT(*) AS count_1
FROM
STF
RIGHT JOIN
PV1
ON
STF.staff_username = PV1.admit_user
GROUP BY
staff_username,
staff_name + ' ' + staff_family ) QRY_CNT_1 INNEER JOIN
(
SELECT
trf_staff_id,
COUNT(trf_staff_id) AS count_2
FROM
dpq
JOIN
stf
ON
trf_staff_id = stf.staff_username
GROUP BY
trf_staff_id ) QRY_CNT_2 ON
QRY_CNT_2.trf_staff_id = QRY_CNT_1.staff_username