Well, since you have different column names in the two tables, I would first try:
select name, parent, subcomp, comp from Fuzzyset where not exists (select 1 from Rules where var_name = parent and subcomp_name = subcomp and comp_name = comp); I also reversed the order, placing the Rules columns on the left side of the comparisons in the subselect. Then try aliasing the outer Fuzzyset, and refer to the alias in the subselect. select name, parent, subcomp, comp from Fuzzyset fs where not exists (select 1 from Rules where var_name = fs.parent and subcomp_name = fs.subcomp and comp_name = fs.comp); This "not exists (select 1 from ...)" has been pretty reliable for me. -Clark ----- Original Message ---- From: Rich Shepard <[EMAIL PROTECTED]> To: Clark Christensen <[EMAIL PROTECTED]> Sent: Thursday, May 15, 2008 5:05:47 PM Subject: Re: [sqlite] Finding Missing Table Rows On Thu, 15 May 2008, Clark Christensen wrote: > I think you need a SELECT statement inside the exists (...) > > Something like > > select name, parent, subcomp, comp from Fuzzyset where not exists > (select 1 from Rules where Fuzzyset.parent=var_name and > Fuzzyset.subcomp=subcomp_name and > Fuzzyset.comp=comp_name); Clark, I tried this better syntax: sqlite> select * from Fuzzyset except select * from Rules where Fuzzyset.parent=Rules.var_name and Fuzzyset.subcomp=Rules.subcomp_name and Fuzzyset.comp=Rules.comp_name; However, it came back with: SQL error: no such column: Fuzzyset.comp. Which is frustrating because that column exists: sqlite> .s Fuzzyset CREATE TABLE Fuzzyset (name TEXT NOT NULL, parent TEXT NOT NULL, subcomp TEXT NOT NULL, comp TEXT NOT NULL, ... Don't know where I'm going off the track. Many thanks, Rich -- Richard B. Shepard, Ph.D. | Integrity Credibility Applied Ecosystem Services, Inc. | Innovation <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
