How to return multiple rows of data when only one unique variable is available for where clause?

Question

I currently have a very simple MySQL query that looks like this:

SET @recordID = 60749;
SELECT netID, ID, recordID, team
FROM NetLog
WHERE recordID = @recordID;

The variable recordID is unique for each row and is the only variable available to get the desired row.

But now I also need to return any other rows that have the same 'team' value as the original row. I tried several variations but it always returns "#1242 - Subquery returned more than 1 row".

SET @recordID = 60749;
SELECT a.netID, a.ID, a.recordID, a.team,
    (SELECT b.recordID FROM NetLog b WHERE b.team = a.team AND b.recordID <> a.recordID) as rID
FROM NetLog a
WHERE a.recordID = @recordID;

I would like to do this in one query if possible.

Can someone point me in the right direction?

Answer 1

Use UNION to combine queries.

WITH mainRow AS (
    SELECT netID, ID, recordID, team
    FROM NetLog
    WHERE recordID = @recordID
)
SELECT * FROM mainRow
UNION
SELECT b.netID, b.ID, b.recordID, b.team
FROM NetLog AS b
JOIN mainRow AS a ON b.team = a.team AND b.recordID <> a.recordID