Search code examples
sqloracle12c

SQL Select - where subquery has multiple row

I'm trying to build a simple select that prompts for childfield and returns results for parent field. In a majority of our situations only one parent is listed but when multiple I get the 'single-row subquery returns more than one row' error that I'm not certain how to work out of.

Select Date, location, MiscData, ChildField, ParentField
from Maintable t1
where childfield = (select t2.parentfield from maintable t2 where t2.childfield = @('prompt', childfield))

As I mentioned for a vast majority of our data will only have one parent field but when there's more than one it error's out. I've started tripping over my own feet so to speak and possibly thinking about this in entirely the wrong way and could use some pointers or at least a push in the right direction.

Just for a little more clarification on my code; the parentfield was once contained in the childfield and that is the results I'm trying to obtain. And when the parentfield was a childfield it would have no parentfield.

I made the below sample set to try to provide an idea of some of the items I'm dealing with. With this set one might visualize how more than one parent can makeup a child which is why we may have multiple row. "and no this isn't for a bar - just a sample set"

Sample Data set:

enter image description here

Solution

  • I reviewed some of the suggestions provided and the best I was able to work from was by Rajat.

    Select Date, location, MiscData, ChildField, ParentField
    from Maintable t1
    INNER JOIN maintable t2 ON t2.parentfield = t1.childfield 
    where t2.childfield = @('prompt', childfield))

    I wasn't receiving any data which I found out was due to the infamous 'space' character that threw everything out of wack.