SQL Subquery - executing a select statement for every "ID" returned in a row
oracle, sql
Solution
Assuming that the specified Person will only have 1 Order between the given Dates and Flag.
SELECT p.personID, p.name, (Select OrderId From Orders o Where o.PersonId = p.personID and o.Date Between Value1 And Value2 And o.FlagColumn = 'Active') As OrderId
FROM Person p
WHERE p.name = "John"
You can also you `JOIN` for this.
SELECT p.personID, p.name, o.OrderId
FROM Person p
JOIN Orders o On p.PersonId = o.PersonId
WHERE p.name = "John" And
o.Date Between Value1 And Value2 And
o.FlagColumn = 'Active'
Problem
Let's say I have two tables, a `person` table and an `orders` table. The `person` table includes a `personID` field and a `name` field, among others. The `orders` table contains an `orderID` field, among others. I want to return information about one or more persons, but in addition to that, I want to use the personID to query additional information, to be returned in each row. For example: ``` personID name + orderID 120 John 5000 ``` My query right now is as follows: ``` SELECT p.personID, p.name FROM person p WHERE p.name = "John" ``` I would like to return a list of people matching that name query, but for each result, also use the personID to look up a specific order (one order) which falls between two dates, has an "active" flag checked, etc, or if that order doesn't exist, return a null for orderID.