Geek asks and answers

SQL: getting a unique structure

I am having trouble finding an exclusive group dataset for a given query parameter.

My table is as follows:

GROUP_ID DOMAIN_ID (unique)
-------- ---------
111 2123
111 2124
111 2125
111 2126
112 2124
112 2125
113 2124
113 2125
113 2126
114 2124
114 2127
114 2128

Ok, now I need to find GROUP_ID, where DOMAIN_ID ONLY contains 2124 and 2125 , that is, it should not return 111 or 113 from the example above.

Restriction: SP / Function cannot be used. This must be a single SQL query.

Thanks so much for your time in advance.

+3
source share
7 answers

.

, , , :

select GROUP_ID
from MY_TABLE oq
where DOMAIN_ID  in (2124, 2125)
group by GROUP_ID
having count(GROUP_ID)=2 and 
count(GROUP_ID) = (select count(iq.DOMAIN_ID) 
from MY_TABLE iq WHERE iq.GROUP_ID=oq.GROUP_ID)
+1

groupid, HAVING , min (domainid) = 2124 max (domainid) = 2125

+1

Here is one way to do this:

SELECT group_id FROM table WHERE group_id IN (SELECT group_id FROM table WHERE domain_id in (2124, 2124)) GROUP BY group_id HAVING count(group_id) = 2;

The advantage of this approach is that the two domain_ids do not have to be sequential, as in one of the other answers.

Using your data in sqlite3, you will get:

sqlite> create table t (a int, b int);
sqlite> .import data t
sqlite> SELECT a FROM t WHERE a IN (SELECT a FROM t WHERE b in (2124, 2124)) GROUP BY a HAVING count(a) = 2;
a
112
+1
source

Are you trying to get something like this?

SELECT DOMAIN_ID, COUNT(*) AS OCCURRENCES FROM TEST WHERE DOMAIN_ID = '2124' OR DOMAIN_ID =     '2125' GROUP BY DOMAIN_ID

leads to:

DOMAIN_ID   OCCURRENCES     2124        4
2125        3
+1
source

If you don't have (group_id, domain_id) duplicates, you can use

SELECT group_id, 
  SUM(CASE WHEN domain_id=2124 OR domain_id=2125 THEN 1 ELSE -1 END) AS matches 
FROM `mytable`
GROUP BY group_id
HAVING matches=2
+1
source 
SELECT GROUP_ID
FROM atable
GROUP BY GROUP_ID
HAVING COUNT(CASE WHEN DOMAIN_ID IN (2124, 2125) THEN 1 END) = 2
   AND COUNT(*) = 2
+1
source 
SELECT DISTINCT GROUP_ID FROM TABLE
WHERE DOMAIN_ID IN (2124, 2125)

Something like that?

-1
source

More articles: