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 -- > > sqlite> SELECT * FROM foo; > a b > ---------- ---------- > 1 6 > 2 6 > 2 3 > 3 3 > 3 4 > 3 5 > 4 7 > 4 8 > 5 3 > 5 4 > 6 9 > 6 10 > 7 11 > 7 12 > 8 13 > 8 14 > 9 7 > 9 15 > 9 16 > 10 17 > > 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> SELECT * FROM foo WHERE b = 3 AND b = 4; > sqlite> SELECT * FROM foo WHERE b = 3 OR b = 4; > a b > ---------- ---------- > 2 3 > 3 3 > 3 4 > 5 3 > 5 4 > sqlite> SELECT * FROM foo WHERE b IN (3, 4);; > a b > ---------- ---------- > 2 3 > 3 3 > 3 4 > 5 3 > 5 4 > sqlite> SELECT * FROM foo WHERE b = 3 > ...> UNION > ...> SELECT * FROM foo WHERE b = 4; > a b > ---------- ---------- > 2 3 > 3 3 > 3 4 > 5 3 > 5 4 > > I guess I need something like > > sqlite> SELECT * FROM ideas_tags WHERE tag_id = 3 > ...> INTERSECT > ...> SELECT * FROM ideas_tags WHERE tag_id = 4; > sqlite> > > You can do it with a self-join:
SELECT foo.a, foo.b FROM foo INNER JOIN foo foo2 ON foo.a = foo2.a WHERE (foo.b = 3 AND foo2.b = 4) OR (foo.b = 4 AND foo2.b = 3) _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
