Checking if specific tuple exists in table

sql sqlite tuples where-in
Acorn picture Acorn · Nov 6, 2011 · Viewed 10k times · Source

Is there a way to check if a specific tuple exists in a table in a where-in statement?

Something like:

create table Test(A int, B int);

insert into Test values (3, 9);
insert into Test values (6, 7);
insert into Test values (7, 6);
insert into Test values (3, 4);

select A, B
from Test
where (B, A) in Test;

Expected output:

6|7
7|6

Answer

FrankieTheKneeMan picture FrankieTheKneeMan · Nov 6, 2011

You were super close, the second half of an "in" clause has to be a select... so

SELECT A,B
FROM Test
WHERE (B,A) IN (SELECT B,A FROM Test);

The test (IN) must be in the same fields (or types of fields)

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
Is it possible to insert multiple rows at a time in an SQLite database?

In MySQL you can insert multiple rows like this: INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); However, I am getting an error when …

Read More