Need help in sql query merge

The reason your second query is not showing a true result is because you're joining the tables without any kind on link. That means that the counts will be multiplied by the other tables. test * usertesting * user1.

So if you had the following tables:

TableA, TableB, TableC
1       6       a
2       7       b
3       8       c

By selecting as you do in the second query you'd get the following results:

AValue, BValue, CValue
1       6       a
1       6       b
1       6       c
1       7       a
1       7       b
1       7       c
1       8       a
1       8       b
1       8       c
2       6       a
2       6       b
2       6       c
2       7       a
2       7       b
2       7       c
2       8       a
2       8       b
2       8       c
3       6       a
3       6       b
3       6       c
3       7       a
3       7       b
3       7       c
3       8       a
3       8       b
3       8       c

Personally I think the union select is the best way to get the data you're after but the following should achieve your desired result.

SELECT cTest, cUserTesting, cUser1 FROM
(SELECT cTest = COUNT(pk_id) FROM test),
(SELECT cUserTesting = COUNT(pk_id) FROM usertesting),
(SELECT cUser1 = COUNT(user_id) FROM user1)

As each of the tables listed only has one row there won't be a multiplication of results.

Although if your problem with the union is you don't know which count is which you could try the following.

select cType = 'test', vCount = count(a.pk_id) from test a
union
select 'usertesting', count(b.pk_id) from usertesting b
union
select 'user1', count(c.user_id) from user1 c;