SQL questions

Question

I have a table A

ID    Term
10    A
10    B
10    C
20    A
20    B
20    E

what's the best way to write a sql to 

 - get ID 10, if I try to find (A,B,C)
 - get NOTHING if I try to find (A,B)
 - get ID 20, if I try to find NOT in (C,D)


Select distinct ID from TableA where Term in (A,B,C) will return both 10 and 20
Select distinct ID from TableA where Term in (A,B) will also return both 10 and 20
Select distinct ID from TableA where Term NOT in (C,D) will also return both 10 and 20

Thanks!

Answer 1

1.

SELECT ID 
FROM TableA
WHERE Term IN ('A','B','C')
GROUP BY ID
HAVING COUNT(ID)=3
LIMIT 1

Here 3 would be the length of the set (A,B,C in this case). 2 & 3 could probably be some variation of the above.

Answer 2

Q1

SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table A(ID smallint, term varchar(1));
sqlite> insert into A values(10, 'A');
sqlite> insert into A values(10, 'B');
sqlite> insert into A values(10, 'C');
sqlite> insert into A values(20, 'A');
sqlite> insert into A values(20, 'B');
sqlite> insert into A values(20, 'E');
sqlite> SELECT ID FROM A WHERE TERM = 'A' INTERSECT SELECT ID FROM A WHERE TERM
= 'B' INTERSECT SELECT ID FROM A WHERE TERM = 'C';
10

Q2

sqlite> SELECT ID FROM A WHERE TERM = 'A' EXCEPT SELECT ID FROM A WHERE TERM = '
B';

returns no results. EXCEPT can also be called MINUS in some versions of SQL

Q3

sqlite> SELECT ID FROM A EXCEPT SELECT ID FROM A WHERE TERM = 'C' UNION SELECT I
D FROM A WHERE TERM = 'D';
20

Answer 3

I assume you want one form of query that can be used to answer all three questions, not a different kind of query for each question.

These solutions take advantage of COUNT() not counting NULLs. When the OUTER JOIN does not match a row in t2, it results in NULL for all columns from t2.

get ID 10, if I try to find (A,B,C)

ID 10 has three distinct term values, and we're searching for all three.

SELECT t1.ID
FROM TableA t1 LEFT OUTER JOIN TableA t2 
  ON (t1.ID = t2.ID AND t1.term = t2.term AND t2.term IN ('A', 'B', 'C'))
GROUP BY t1.ID
HAVING COUNT(t1.term) = COUNT(t2.term);

get NOTHING if I try to find (A,B)

Both ID 10 and ID 20 have three distinct term values, but our search is only for two. The counts are 3 = 2 for both IDs, so neither have equal counts.

SELECT t1.ID
FROM TableA t1 LEFT OUTER JOIN TableA t2 
  ON (t1.ID = t2.ID AND t1.term = t2.term AND t2.term IN ('A', 'B'))
GROUP BY t1.ID
HAVING COUNT(t1.term) = COUNT(t2.term);

get ID 20, if I try to find NOT in (C,D)

ID 20 has three distinct term values, and all three of them are NOT 'C' or 'D'. So the counts are equal.

SELECT t1.ID
FROM TableA t1 LEFT OUTER JOIN TableA t2 
  ON (t1.ID = t2.ID AND t1.term = t2.term AND t2.term NOT IN ('C', 'D'))
GROUP BY t1.ID
HAVING COUNT(t1.term) = COUNT(t2.term);

Answer 4

CREATE TABLE mySearch (
    search_id       INT,
    val             CHAR(1),
    is_required     INT,
    is_excluded     INT
    )  

INSERT INTO mySearch VALUES (1, 'A', 1, 0)   -- Search1 : A is required  
INSERT INTO mySearch VALUES (1, 'B', 1, 0)   -- Search1 : B is required  
INSERT INTO mySearch VALUES (1, 'C', 1, 0)   -- Search1 : C is required  

INSERT INTO mySearch VALUES (2, 'A', 1, 0)   -- Search2 : A is required  
INSERT INTO mySearch VALUES (2, 'B', 1, 0)   -- Search2 : B is required  

INSERT INTO mySearch VALUES (3, 'C', 0, 1)   -- Search3 : C is excluded
INSERT INTO mySearch VALUES (3, 'D', 0, 1)   -- Search3 : D is excluded  

SELECT
    [search].search_id,
    [data].id
FROM
    TableA            AS [data]
LEFT JOIN
    my_search         AS [search]
        ON [search].val = [data].Term
GROUP BY
    [search].search_id,
    [data].ID
HAVING
    ISNULL(SUM([search].is_included),0) = (SELECT SUM(is_included) FROM mySearch WHERE search_id = [search].search_id)
    AND MAX([search].is_excluded) IS NULL

Should satisfy all three seaches in one query. Unfortunately I can't test it as I'm at the in-laws house and they don't have geek toys to test on ;)