I believe what you are looking for is the relational operator called DIVIDE, which is one of the original ones that E. F. Codd defined (along with (natural inner) JOIN, UNION, MINUS, PROJECT, RESTRICT, etc) but that very few if any SQL DBMSs actually implement directly, which is a crying shame.
Fortunately you can simulate DIVIDE using a combination of other relational operators, so it is still possible to do this in SQLite. For example, you can do it with a nested pair of NOT EXISTS subqueries. DIVIDE is basically the opposite of JOIN (or more specifically, cartesian product), where JOIN is analogous to a multiplication (and so is INTERSECT, and UNION is analogous to sum, and MINUS self-explanatory). An example of where you might use DIVIDE is with the query "who are the pilots where for each pilot that pilot can fly every plane in this hangar", so only the pilots that match every plane are returned and those that match just some aren't. (DIVIDE also has the analogy of divide by zero, which comes up if the total number of planes period is empty; in that case every pilot is returned. Or some people might say none.) See http://en.wikipedia.org/wiki/Relational_algebra#Division for an explanation of DIVIDE and what its useful for. See also http://www.dbazine.com/ofinterest/oi-articles/celko1 for an explanation of DIVIDE that shows how to simulate it with standard SQL. In your case, you would use a DIVIDE like this: SELECT * FROM bar; b ---- 3 4 SELECT * FROM (foo DIVIDE bar); a ---- 3 5 You can probably see how that's the the opposite of JOIN. If the result of the DIVIDE above were in tables quux, then you could get the original bar rows back by saying: SELECT * FROM quux JOIN bar Now it would be really great if SQLite would help lead the pack and implement a DIVIDE operator natively, which is just shorthand syntax for SQL it already supports. -- Darren Duncan P Kishor wrote: > I don't even know how to title this post, and it just might be > something very obvious. Either way, I apologize in advance. Consider > the following -- > > I want the values of ‘a’ for which ‘b’ = 3 AND ‘b’ = 4 (This is just > an example. In reality, b could be any set of numbers, not just two > numbers). To illustrate -- > sqlite> SELECT * FROM foo WHERE b = 3; > a b > ---------- ---------- > 2 3 > 3 3 > 5 3 > sqlite> SELECT * FROM foo WHERE b = 4; > a b > ---------- ---------- > 3 4 > 5 4 > > So, I really want only the following rows > 3 3 > 5 3 > 3 4 > 5 4 > > I don’t want > > 2 3 > > because there is no > > 2 4 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
