CREATE VIEW of two tables with unique rows

0
votes

I want to CREATE VIEW of two tables, merge them, where column from table1 != column from table2 and later insert the rows that do not have duplicate columns.

CREATE TABLE IF NOT EXISTS test(Birds, Animals, Air, Earth);
INSERT INTO test VALUES("Crow", "Dog", "Oxygen", "Not Flat");

CREATE TABLE IF NOT EXISTS test1 (Birds, Animals, Air, Earth, Sky);
INSERT INTO test1 VALUES("Crow", "Dog", "Oxygen", "Not Flat", "Blue");
INSERT INTO test1 VALUES("Eagle", "Dog", "Oxygen", "Not Flat", "Blue");

CREATE VIEW view_name AS SELECT * FROM test, test1 WHERE test.Birds != test1.Birds'

test Table:

Birds|Animals|Air|Earth
Crow|Dog|Oxygen|Not Flat

test1 Table:

Birds|Animals|Air|Earth|Sky
Crow|Dog|Oxygen|Not Flat|Blue
Eagle|Dog|Oxygen|Not Flat|Blue

Current output:

Birds|Animals|Air|Earth|Birds:1|Animals:1|Air:1|Earth:1|Sky
Crow|Dog|Oxygen|Not Flat|Eagle|Dog|Oxygen|Not Flat|Blue

Desired output:

Birds|Animals|Air|Earth|Sky
Eagle|Dog|Oxygen|Not Flat|Blue
2
sqlsqlite
SELECT Birds, Animals, Air, Earth FROM test1 EXCEPT SELECT Birds, Animals, Air, Earth FROM test? Not clear exactly which columns you want matched - Charlieface
Error: near "Birds": syntax error | Want to match Birds - Akmal Soliev
Sorry edited. But noticed you want the Sky column which is not present in test. So try SELECT Birds, Animals, Air, Earth, Sky FROM test1 WHERE EXISTS (SELECT test1.Birds, test1.Animals, test1.Air, test1.Earth EXCEPT SELECT test.Birds, test.Animals, test.Air, test.Earth FROM test) - Charlieface
This one works perfectly fine! I think I can shorten that to: SELECT * FROM test1 WHERE EXISTS (SELECT Birds EXCEPT SELECT Birds FROM test); - Akmal Soliev
I see that it puts a priority on test1 table yeah? That every column from test1 would be used. - Akmal Soliev

2 Answers

0
votes

You can solve that problem in different ways. However, I have a solution for you. Please check the query and let me know =>

DECLARE @test TABLE(Birds VARCHAR(50), Animals VARCHAR(50), Air VARCHAR(50), Earth VARCHAR(50)) ;
INSERT INTO @test VALUES('Crow', 'Dog', 'Oxygen', 'Not Flat');

DECLARE @test1 TABLE (Birds VARCHAR(50), Animals VARCHAR(50), Air VARCHAR(50), Earth VARCHAR(50),Sky VARCHAR(50));
INSERT INTO @test1 VALUES('Crow', 'Dog', 'Oxygen', 'Not Flat', 'Blue');
INSERT INTO @test1 VALUES('Eagle', 'Dog', 'Oxygen', 'Not Flat', 'Blue');

CREATE VIEW view_name AS
SELECT t1.* FROM @test t 
INNER JOIN @test1 t1 ON t.Birds<>t1.Birds

NOTE: This code is written in SQL Server.

0
votes

It depends exactly which columns you want to compare. If it's just the one column, you can do this:

SELECT Birds, Animals, Air, Earth, Sky
FROM test1
WHERE EXISTS
    (SELECT test1.Birds
    EXCEPT
    SELECT test.Birds
    FROM test);

You can also flip this round to NOT EXISTS...INTERSECT and get the same result.

This can also be rewritten as an EXISTS ... =:

SELECT Birds, Animals, Air, Earth, Sky
FROM test1
WHERE NOT EXISTS (SELECT 1
    FROM test
    WHERE test.Birds = test1.Birds );

If you need also the opposing results from test you can do the same thing in reverse, then UNION the result.