How to return multiple rows of data when only one unique variable is available for where clause?-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW)

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

P粉094351878 2023-09-13 17:31:35

346

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?

P粉094351878

reply all(1)

P粉3788901062023-09-14 11:42:17 1 floor

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