MYSQL syntax, merge two queries

Question

I want a table identifier to be on count(*) so that I can combine these two queries Query 1

SELECT
    COUNT(*) AS COUNT
FROM
    Table1 OP
WHERE
    OP.TARGET_ID=?

Query2

SELECT
    T.TARGET_NAME, T.TARGET_PUBLIC_NAME
FROM
    KB_TARGETS T
WHERE
    T.TARGET_ID = ?;

into

SELECT
    T.TARGET_NAME, T.TARGET_PUBLIC_NAME, OP.COUNT(*) AS COUNT
FROM
    Table2 T,Table1 OP
WHERE
    T.TARGET_ID = ?;

Please help with the syntax. I belive I need a table identifier on count but don't know how to do that

Thanks

Answer 1

As I interpret it, the simplest way to use a correlated subquery:

SELECT T.TARGET_NAME, T.TARGET_PUBLIC_NAME,
       (SELECT COUNT(*) AS COUNT
        FROM Table1 OP
        WHERE OP.TARGET_ID = T.TARGET_ID
       ) as cnt
FROM KB_TARGETS T
WHERE T.TARGET_ID = ?;

You have to be very careful with using a JOIN. The COUNT() will be incorrect if TARGET_ID is on multiple rows of KB_TARGETS. The correlated subquery is a much safer solution.

Answer 2

SELECT
    T.TARGET_NAME, T.TARGET_PUBLIC_NAME, OP.COUNT(*) AS COUNT
FROM
    Table2 T join Table1 OP on T.TARGET_ID=P.TARGET_ID
WHERE
    T.TARGET_ID = ?;
group by T.TARGET_NAME, T.TARGET_PUBLIC_NAME