0

To make a question simplier let's suppose we are having two tables with few columns and one-to-many relation...

  1. first with fields id, some_field and second_id.
  2. second with fields id, parent_id

First I do

SELECT first.id, first.some_field, second.id, second.parent_id 
FROM first INNER JOIN second ON first.second_id = second.id 
WHERE some_field="some_val"

But after that I need to get first.id, first.some_field, second.id from join of two tables where parent_id is among values in respective column of result of the above query.

Is that can be done with one query? Or (on PHP side) I need to take column in result set and do something like the following?

SELECT first.id, first.some_field, second.id, second.parent_id 
FROM first INNER JOIN second ON first.second_id = second.id 
WHERE parent_id IN (<many_ids_here>)
Eugeny89
  • 3,797
  • 7
  • 51
  • 98
  • This is done as a hierarchical query or a recursive query. Unfortunately mySQL doesn't support these concepts. There is a work around using global variables: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query or you have to repeat joins between tables for the # of levels you wish to support. – xQbert May 15 '17 at 13:00

1 Answers1

1

You can create a nested query, like this:

SELECT first.id, first.some_field, second.id, second.parent_id 
FROM first INNER JOIN second ON first.second_id = second.id 
WHERE parent_id IN (SELECT DISTINCT second.parent_id FROM first INNER JOIN second ON first.second_id = second.id WHERE some_field="some_val")
Vishal Gupta
  • 124
  • 6