Okay, I'm trying to run a query that incorporates a subquery. Or maybe I don't need a subquery, fuck if I know.
I have a database that contains, among other things, the fields IDnumber, firstname, lastname, and ParentID. The ParentID is how the database matches related records together. If the record is related to another record, the ParentID contains the IDnumber of the other record. If the record isn't related to anything, the ParentID just contains the IDnumber of its own record.
So you might have something that looks like:
IDnumber FirstName LastName ParentID
00001 Joe Smith 00001
00002 Bob Smithie 00002
00003 Ed Eddies 00003
00004 George Brown 00001
00005 Matt Eddies 00003
I want to run a search that pulls values from a form for the IDnumber, firstname, and lastname fields, and returns all records which match based on those values, AND all records that are related to anything that matched. If, for example, the LastName search field contains "smith", I want the query to return the records for 00001, 00002, and 00004.
I tried doing this by doing an initial query for just the records that match the search fields (00001 and 00002), and then feeding the results of that query into a second query (basically returning all records for which either the IDnumber OR the ParentID existed in the list of IDnumbers returned by the first query), but it yells at me because "at most one query can be returned by this subquery". And I don't know how to work around that. My subquery-fu is weak, and I don't really speak SQL.
It's entirely possible that a subquery isn't the way to go here, but I'm not sure how to accomplish what I need to accomplish.
Maddie: "I named my feet. The left one is flip and the right one is flop. Oh, and also I named my flip-flops."I make tweet.