These suggestions from Clemens will work exactly as you want, but I need to add that it assumes the records all perfectly match between the tables, even flags, ID column etc.

This means, if it doesn't work as you expect, you can still use the exact same methods but you can of course include the relevant columns (that should or shouldn't match between tables) in stead of the * wildcard.


As an example based on Clemens' first example, the query:

SELECT DDatum FROM Katalog EXCEPT SELECT DDatum FROM ZKatalog;

will produce a list of all dates in Katalog that doesn't have a corresponding date in ZKatalog.

Cheers,
Ryan


On 2017/09/06 1:09 PM, Clemens Ladisch wrote:
Joe wrote:
my SQLite database has two tables Katalog and ZKatalog with the same structure. 
One of the columns  is called DDatum. What's the most efficient way to

(1) Select records, which are only in Katalog, but not in ZKatalog?
SELECT * FROM Katalog EXCEPT SELECT * FROM ZKatalog;

(2) Select records, which are in Katalog and in ZKatalog?
SELECT * FROM Katalog INTERSECT SELECT * FROM ZKatalog;
SELECT * FROM Katalog UNION SELECT * FROM ZKatalog;
(depending on the exact meaning of the word "and" in your question)

(3) Select records, which are with same column values except DDatum in Katalog 
and in ZKatalog?
SELECT *
FROM Katalog
WHERE the_primary_key_column IN (
   SELECT the_primary_key_column FROM (
     SELECT all,columns,except,ddatum FROM Katalog
     INTERSECT
     SELECT all,columns,except,ddatum FROM ZKatalog
   )
);

(If you do not need the DDatum values, use only the inner subquery.)

(4) Select records from Katalog and ZKatalog with same DDatum content?
SELECT * FROM Katalog WHERE DDatum IN (SELECT DDatum FROM ZKatalog);


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to