MySQL select over the result set again-CodePudding

EDIT, because wrog problem description (sorry for that!)

I need a query in MySQL that loops through the selected rows and creates a result array. The pattern for this is shown below.

Currently I call a select again and again. Is there a way to create a single query from this? Thanks a lot!

Table structure

ID, VID, AID, ... (title, etc.) ... TID

Select over the vid field

SELECT id, vid, aid FROM tbl WHERE vid = 24;

ID  VID AID
1   24  NULL
2   24  NULL
3   24  712
4   24  713
5   24  714
6   24  715
7   24  716
...

If aid not null => select the same table with the AID value as condition for another field

SELECT * FROM tbl WHERE TID = 112
SELECT * FROM tbl WHERE TID = 113 
SELECT * FROM tbl WHERE TID = 114 
SELECT * FROM tbl WHERE TID = 115 
SELECT * FROM tbl WHERE TID = 116

Result should be (all null rows and the selected rows from above)

ID  VID AID   ...
1   24  NULL
2   24  NULL
75  24  712
79  24  713
88  24  714
92  24  715
97  24  716

The problem in this e.g. is that i need a loop and tones of selects. Pseudocode:

rows = [] 
foreach (main_select)
{
    if (aid != null)
    {
        selected_row = select * from tbl where tid = aid
        rows[] = selected_row
    }
    else
    {
        rows[] = main_select_row_from_iteration
    }
}

CodePudding user response：

You should be able to do all of that with a single query

SELECT tbl1.ID as TBL1_ID, tbl1.VID as TBL1_VID, tbl1.AID as TBL1_AID,
       tbl2.ID as TBL2_ID, tbl2.VID as TBL2_VID, tbl2.AID as TBL2_AID 
FROM tbl AS tbl1
LEFT JOIN tbl AS tbl2 ON tbl1.AID = tbl2.TID
WHERE tbl1.VID = 24 AND tbl1.AID IS NOT NULL

It's a self-join of the same table, the WHERE condition makes sure that you have the initial VID and that there's not NULL value in AID. The JOIN takes AID and matches it to TID.

The fields in the SELECT list are from both, separated via alias.