SQL Subquery - executing a select statement for every "ID" returned in a row

Question

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.

Answer 1

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'