Extract tuples with specified common values in another column in SQL

up vote
6
down vote

favorite

I have a dataset that look like:

 Col1 Col2 
 1 ABC 
 2 DEF
 3 ABC 
 1 DEF

Expected output:

Col1 Col2 
 1 ABC 
 1 DEF

I want to extract only those IDSs from Col1 which have both values ABC and DEF in the column.

I tried the self-join in SQL but that did not give me the expected result.

SELECT DISTINCT Col1
FROM db A, db B
WHERE A.ID <> B.ID
 AND A.Col2 = 'ABC'
 AND B.Col2 = 'DEF' 
GROUP BY A.Col1

Also, I tried to the same thing in R using the following code:

vc <- c("ABC", "DEF")
data1 <- db[db$Col2 %in% vc,]

Again, I did not get the desired output. Thanks for all the pointers in advance.

edited 3 hours ago

Tim Biegeleisen

197k1377125

asked 3 hours ago

Priyanka Indapurkar

312

New contributor

add a commentÂ |Â

up vote
6
down vote

favorite

I have a dataset that look like:

 Col1 Col2 
 1 ABC 
 2 DEF
 3 ABC 
 1 DEF

Expected output:

Col1 Col2 
 1 ABC 
 1 DEF

I want to extract only those IDSs from Col1 which have both values ABC and DEF in the column.

I tried the self-join in SQL but that did not give me the expected result.

SELECT DISTINCT Col1
FROM db A, db B
WHERE A.ID <> B.ID
 AND A.Col2 = 'ABC'
 AND B.Col2 = 'DEF' 
GROUP BY A.Col1

Also, I tried to the same thing in R using the following code:

vc <- c("ABC", "DEF")
data1 <- db[db$Col2 %in% vc,]

Again, I did not get the desired output. Thanks for all the pointers in advance.

edited 3 hours ago

Tim Biegeleisen

197k1377125

asked 3 hours ago

Priyanka Indapurkar

312

New contributor

add a commentÂ |Â

up vote
6
down vote

favorite

I have a dataset that look like:

 Col1 Col2 
 1 ABC 
 2 DEF
 3 ABC 
 1 DEF

Expected output:

Col1 Col2 
 1 ABC 
 1 DEF

I want to extract only those IDSs from Col1 which have both values ABC and DEF in the column.

I tried the self-join in SQL but that did not give me the expected result.

SELECT DISTINCT Col1
FROM db A, db B
WHERE A.ID <> B.ID
 AND A.Col2 = 'ABC'
 AND B.Col2 = 'DEF' 
GROUP BY A.Col1

Also, I tried to the same thing in R using the following code:

vc <- c("ABC", "DEF")
data1 <- db[db$Col2 %in% vc,]

Again, I did not get the desired output. Thanks for all the pointers in advance.

edited 3 hours ago

Tim Biegeleisen

197k1377125

asked 3 hours ago

Priyanka Indapurkar

312

New contributor

I have a dataset that look like:

 Col1 Col2 
 1 ABC 
 2 DEF
 3 ABC 
 1 DEF

Expected output:

Col1 Col2 
 1 ABC 
 1 DEF

I want to extract only those IDSs from Col1 which have both values ABC and DEF in the column.

I tried the self-join in SQL but that did not give me the expected result.

SELECT DISTINCT Col1
FROM db A, db B
WHERE A.ID <> B.ID
 AND A.Col2 = 'ABC'
 AND B.Col2 = 'DEF' 
GROUP BY A.Col1

Also, I tried to the same thing in R using the following code:

vc <- c("ABC", "DEF")
data1 <- db[db$Col2 %in% vc,]

Again, I did not get the desired output. Thanks for all the pointers in advance.

mysql r

edited 3 hours ago

Tim Biegeleisen

197k1377125

asked 3 hours ago

Priyanka Indapurkar

312

New contributor

edited 3 hours ago

Tim Biegeleisen

197k1377125

asked 3 hours ago

Priyanka Indapurkar

312

New contributor

edited 3 hours ago

Tim Biegeleisen

197k1377125

edited 3 hours ago

Tim Biegeleisen

197k1377125

edited 3 hours ago

Tim Biegeleisen

197k1377125

asked 3 hours ago

Priyanka Indapurkar

312

New contributor

asked 3 hours ago

Priyanka Indapurkar

312

asked 3 hours ago

Priyanka Indapurkar

312

New contributor

Priyanka Indapurkar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a commentÂ |Â

4 Answers
4

active

oldest

votes

up vote
6
down vote

In R, you could do

library(dplyr) 
df %>% 
 group_by(Col1) %>% 
 filter(all(vc %in% Col2))

# Col1 Col2 
# <int> <fct>
#1 1 ABC 
#2 1 DEF

The Base R equivalent of that would be

df[as.logical(with(df, ave(Col2, Col1, FUN = function(x) all(vc %in% x)))), ]

# Col1 Col2
#1 1 ABC
#4 1 DEF

We select the groups which has all of vc in them.

edited 2 hours ago

zx8754

27.6k76394

answered 3 hours ago

Ronak Shah

24.4k93551

add a commentÂ |Â

up vote
3
down vote

Here is your current query corrected:

SELECT DISTINCT t1.Col1
FROM yourTable t1
INNER JOIN yourTable t2
 ON t1.Col1 = t2.Col1
WHERE t1.Col2 = 'ABC' AND t2.Col2 = 'DEF';

Demo

The join condition is that both Col1 values are the same, the first Col2 value is ABC and the second Col2 value is DEF.

But, I would probably use the following canonical approach to this:

SELECT Col1
FROM yourTable
WHERE Col2 IN ('ABC', 'DEF')
GROUP BY Col1
HAVING MIN(Col2) <> MAX(Col2);

answered 3 hours ago

Tim Biegeleisen

197k1377125

add a commentÂ |Â

up vote
0
down vote

Use correlated subquery:

select * from tablename t 
where exists (select 1 from tablename t1 where t1.col1=t.col1 and col2 in ('ABC','DEF')
group by col1 having count(distinct col2)=2)

answered 3 hours ago

fa06

4,612413

add a commentÂ |Â

up vote
0
down vote

Here's a way using group_concat

select t.Col1,t.col2
from t
join
(
select col1,group_concat(distinct col2 order by col2) gc
from t
group by col1 having gc = 'abc,def'
) s
on s.col1 = t.col1;

+------+------+
| Col1 | col2 |
+------+------+
| 1 | ABC |
| 1 | DEF |
+------+------+
2 rows in set (0.16 sec)

But you do have to understand the order that col2 will be in

answered 3 hours ago

P.Salmon

6,9472415

add a commentÂ |Â

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

Priyanka Indapurkar is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52620832%2fextract-tuples-with-specified-common-values-in-another-column-in-sql%23new-answer', 'question_page');

);

Post as a guest

Name

4 Answers
4

active

oldest

votes

4 Answers
4

active

oldest

votes

up vote
6
down vote

In R, you could do

library(dplyr) 
df %>% 
 group_by(Col1) %>% 
 filter(all(vc %in% Col2))

# Col1 Col2 
# <int> <fct>
#1 1 ABC 
#2 1 DEF

The Base R equivalent of that would be

df[as.logical(with(df, ave(Col2, Col1, FUN = function(x) all(vc %in% x)))), ]

# Col1 Col2
#1 1 ABC
#4 1 DEF

We select the groups which has all of vc in them.

edited 2 hours ago

zx8754

27.6k76394

answered 3 hours ago

Ronak Shah

24.4k93551

add a commentÂ |Â

up vote
6
down vote

In R, you could do

library(dplyr) 
df %>% 
 group_by(Col1) %>% 
 filter(all(vc %in% Col2))

# Col1 Col2 
# <int> <fct>
#1 1 ABC 
#2 1 DEF

The Base R equivalent of that would be

df[as.logical(with(df, ave(Col2, Col1, FUN = function(x) all(vc %in% x)))), ]

# Col1 Col2
#1 1 ABC
#4 1 DEF

We select the groups which has all of vc in them.

edited 2 hours ago

zx8754

27.6k76394

answered 3 hours ago

Ronak Shah

24.4k93551

add a commentÂ |Â

up vote
6
down vote

In R, you could do

library(dplyr) 
df %>% 
 group_by(Col1) %>% 
 filter(all(vc %in% Col2))

# Col1 Col2 
# <int> <fct>
#1 1 ABC 
#2 1 DEF

The Base R equivalent of that would be

df[as.logical(with(df, ave(Col2, Col1, FUN = function(x) all(vc %in% x)))), ]

# Col1 Col2
#1 1 ABC
#4 1 DEF

We select the groups which has all of vc in them.

edited 2 hours ago

zx8754

27.6k76394

answered 3 hours ago

Ronak Shah

24.4k93551

In R, you could do

library(dplyr) 
df %>% 
 group_by(Col1) %>% 
 filter(all(vc %in% Col2))

# Col1 Col2 
# <int> <fct>
#1 1 ABC 
#2 1 DEF

The Base R equivalent of that would be

df[as.logical(with(df, ave(Col2, Col1, FUN = function(x) all(vc %in% x)))), ]

# Col1 Col2
#1 1 ABC
#4 1 DEF

We select the groups which has all of vc in them.

edited 2 hours ago

zx8754

27.6k76394

answered 3 hours ago

Ronak Shah

24.4k93551

edited 2 hours ago

zx8754

27.6k76394

edited 2 hours ago

zx8754

27.6k76394

edited 2 hours ago

zx8754

27.6k76394

answered 3 hours ago

Ronak Shah

24.4k93551

answered 3 hours ago

Ronak Shah

24.4k93551

answered 3 hours ago

Ronak Shah

24.4k93551

add a commentÂ |Â

up vote
3
down vote

Here is your current query corrected:

SELECT DISTINCT t1.Col1
FROM yourTable t1
INNER JOIN yourTable t2
 ON t1.Col1 = t2.Col1
WHERE t1.Col2 = 'ABC' AND t2.Col2 = 'DEF';

Demo

The join condition is that both Col1 values are the same, the first Col2 value is ABC and the second Col2 value is DEF.

But, I would probably use the following canonical approach to this:

SELECT Col1
FROM yourTable
WHERE Col2 IN ('ABC', 'DEF')
GROUP BY Col1
HAVING MIN(Col2) <> MAX(Col2);

answered 3 hours ago

Tim Biegeleisen

197k1377125

add a commentÂ |Â

up vote
3
down vote

Here is your current query corrected:

SELECT DISTINCT t1.Col1
FROM yourTable t1
INNER JOIN yourTable t2
 ON t1.Col1 = t2.Col1
WHERE t1.Col2 = 'ABC' AND t2.Col2 = 'DEF';

Demo

The join condition is that both Col1 values are the same, the first Col2 value is ABC and the second Col2 value is DEF.

But, I would probably use the following canonical approach to this:

SELECT Col1
FROM yourTable
WHERE Col2 IN ('ABC', 'DEF')
GROUP BY Col1
HAVING MIN(Col2) <> MAX(Col2);

answered 3 hours ago

Tim Biegeleisen

197k1377125

add a commentÂ |Â

up vote
3
down vote

Here is your current query corrected:

SELECT DISTINCT t1.Col1
FROM yourTable t1
INNER JOIN yourTable t2
 ON t1.Col1 = t2.Col1
WHERE t1.Col2 = 'ABC' AND t2.Col2 = 'DEF';

Demo

The join condition is that both Col1 values are the same, the first Col2 value is ABC and the second Col2 value is DEF.

But, I would probably use the following canonical approach to this:

SELECT Col1
FROM yourTable
WHERE Col2 IN ('ABC', 'DEF')
GROUP BY Col1
HAVING MIN(Col2) <> MAX(Col2);

answered 3 hours ago

Tim Biegeleisen

197k1377125

Here is your current query corrected:

SELECT DISTINCT t1.Col1
FROM yourTable t1
INNER JOIN yourTable t2
 ON t1.Col1 = t2.Col1
WHERE t1.Col2 = 'ABC' AND t2.Col2 = 'DEF';

Demo

The join condition is that both Col1 values are the same, the first Col2 value is ABC and the second Col2 value is DEF.

But, I would probably use the following canonical approach to this:

SELECT Col1
FROM yourTable
WHERE Col2 IN ('ABC', 'DEF')
GROUP BY Col1
HAVING MIN(Col2) <> MAX(Col2);

answered 3 hours ago

Tim Biegeleisen

197k1377125

answered 3 hours ago

Tim Biegeleisen

197k1377125

answered 3 hours ago

Tim Biegeleisen

197k1377125

answered 3 hours ago

Tim Biegeleisen

197k1377125

add a commentÂ |Â

up vote
0
down vote

Use correlated subquery:

select * from tablename t 
where exists (select 1 from tablename t1 where t1.col1=t.col1 and col2 in ('ABC','DEF')
group by col1 having count(distinct col2)=2)

answered 3 hours ago

fa06

4,612413

add a commentÂ |Â

up vote
0
down vote

Use correlated subquery:

select * from tablename t 
where exists (select 1 from tablename t1 where t1.col1=t.col1 and col2 in ('ABC','DEF')
group by col1 having count(distinct col2)=2)

answered 3 hours ago

fa06

4,612413

add a commentÂ |Â

up vote
0
down vote

Use correlated subquery:

select * from tablename t 
where exists (select 1 from tablename t1 where t1.col1=t.col1 and col2 in ('ABC','DEF')
group by col1 having count(distinct col2)=2)

answered 3 hours ago

fa06

4,612413

Use correlated subquery:

select * from tablename t 
where exists (select 1 from tablename t1 where t1.col1=t.col1 and col2 in ('ABC','DEF')
group by col1 having count(distinct col2)=2)

answered 3 hours ago

fa06

4,612413

answered 3 hours ago

fa06

4,612413

answered 3 hours ago

fa06

4,612413

answered 3 hours ago

fa06

4,612413

add a commentÂ |Â

up vote
0
down vote

Here's a way using group_concat

select t.Col1,t.col2
from t
join
(
select col1,group_concat(distinct col2 order by col2) gc
from t
group by col1 having gc = 'abc,def'
) s
on s.col1 = t.col1;

+------+------+
| Col1 | col2 |
+------+------+
| 1 | ABC |
| 1 | DEF |
+------+------+
2 rows in set (0.16 sec)

But you do have to understand the order that col2 will be in

answered 3 hours ago

P.Salmon

6,9472415

add a commentÂ |Â

up vote
0
down vote

Here's a way using group_concat

select t.Col1,t.col2
from t
join
(
select col1,group_concat(distinct col2 order by col2) gc
from t
group by col1 having gc = 'abc,def'
) s
on s.col1 = t.col1;

+------+------+
| Col1 | col2 |
+------+------+
| 1 | ABC |
| 1 | DEF |
+------+------+
2 rows in set (0.16 sec)

But you do have to understand the order that col2 will be in

answered 3 hours ago

P.Salmon

6,9472415

add a commentÂ |Â

up vote
0
down vote

Here's a way using group_concat

select t.Col1,t.col2
from t
join
(
select col1,group_concat(distinct col2 order by col2) gc
from t
group by col1 having gc = 'abc,def'
) s
on s.col1 = t.col1;

+------+------+
| Col1 | col2 |
+------+------+
| 1 | ABC |
| 1 | DEF |
+------+------+
2 rows in set (0.16 sec)

But you do have to understand the order that col2 will be in

answered 3 hours ago

P.Salmon

6,9472415

Here's a way using group_concat

select t.Col1,t.col2
from t
join
(
select col1,group_concat(distinct col2 order by col2) gc
from t
group by col1 having gc = 'abc,def'
) s
on s.col1 = t.col1;

+------+------+
| Col1 | col2 |
+------+------+
| 1 | ABC |
| 1 | DEF |
+------+------+
2 rows in set (0.16 sec)

But you do have to understand the order that col2 will be in

answered 3 hours ago

P.Salmon

6,9472415

answered 3 hours ago

P.Salmon

6,9472415

answered 3 hours ago

P.Salmon

6,9472415

answered 3 hours ago

P.Salmon

6,9472415

add a commentÂ |Â

Priyanka Indapurkar is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Priyanka Indapurkar is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky