There is a database with the following tables:
table_1: id, atr1
table_2: id, atr2
table_3: id, table_1_id(Foreign Key), table_2_id(Foreign Key), atr3
The operation I want to make is a bit complicated and I can explain it doing some extra programming aside from the SQL queries.
The first part of the operation would be to get all the identifiers of table_2 in table_3 which meet some criteria.
SELECT table_3.table_2_id
FROM table_3
WHERE
table_3.atr3 = 'value1' AND
table_3.table_1_id = 'value2'
The next part would be to take each of the values I have just obtained and then use them in the following query. I would do this in a loop.
SELECT table_3.id, table_3.atr3, table_1.atr1, table_2.atr2
FROM table_3
JOIN table_1 ON table_3.table_1_id = table_1.id
JOIN table_2 ON table_3.table_2_id = table_2.id
WHERE
table_3.table_2_id = 'current_value_i_am_iterating_over' AND
(table_3.table_1_id = 'value4' OR
table_3.table_1_id = 'value5')
After the query, I would concatenate the obtained rows in an array that contains all the rows I obtain in the successive iterations of the loop.
What I would like to know is if there's a way to avoid the second loop, ideally making the whole operation in only one SQL query.
Can't you use simple sub query? I mean you can have
table_3.table_2_id = (your first query with limit 1)
limit 1 just to avoid multiple records return from subquery
or else you can use IN
table_3.table_2_id IN (your first query)