One way of doing it: SELECT IFNULL(V1.name,V2.name) AS VName, CASE WHEN V1.name=V2.name THEN 0 ELSE -1 END AS VInd FROM v AS V1 LEFT JOIN v AS V2 ON (V1.vid<>V2.vid) AND (V1.name=V2.name) WHERE V1.vid=1 UNION SELECT IFNULL(V1.name,V2.name) AS VName, CASE WHEN V1.name=V2.name THEN 0 ELSE 1 END AS VInd FROM v AS V1 LEFT JOIN v AS V2 ON (V1.vid<>V2.vid) AND (V1.name=V2.name) WHERE V1.vid=2;
Running that on your table yields: VName "VInd" bar 0 baz 1 foo -1 I'm sure someone will have a more succint or optimized version soon :) On 2014/02/08 12:03, Stephan Beal wrote:
Hi, list, most of the time i judge my SQL skills as mediocre, but at times like this i feel like a complete noob... i have table containing a mapping of logic dataset versions and filenames contained in that dataset version: CREATE TABLE v(vid,name); INSERT INTO "v" VALUES(1,'foo'); INSERT INTO "v" VALUES(1,'bar'); INSERT INTO "v" VALUES(2,'bar'); INSERT INTO "v" VALUES(2,'baz'); i am trying like mad to, but can't seem formulate a query with 2 version number inputs (1 and 2 in this case) and creates a result set with these columns: - name. must include all names across both versions - status: -1 if in version 1 but not v2, 0 if in both, 1 if in v2 but not v1. So the above data set should produce: foo, -1 bar, 0 baz, 1 My SQL skills fail me miserably, though. i have no sqlite3 minimum version requirements (am working from the trunk) and am free to use recursive select if necessary, but my instinct says that this should be possible with joins and a CASE (for the status). Any prods in the right direction would be much appreciated,
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
