Select rows that must contain certain values
1
I have two tables named Table A and Table B. Table A is a list of words and Table B is a list of documents related to those words. I need to do a search where I need to retrieve which documents have all the words I look for or some of them.
The tables structures and data example is as follow:
Declare @TableA Table (IdWord Int, Word Varchar(100))
Declare @TableB Table (IdProcess Int, IdWord Int, Document Varchar(100))
Insert Into @TableA Values (1, 'A');
Insert Into @TableA Values (2, 'B');
Insert Into @TableA Values (3, 'C');
Insert Into @TableA Values (4, 'D');
Insert Into @TableA Values (5, 'E');
Insert Into @TableB Values (1, 1, 'Document 1.doc');
Insert Into @TableB Values (1, 2, 'Document 1.doc');
Insert Into @TableB Values (1, 3, 'Document 1.doc');
Insert Into @TableB Values (1, 4, 'Document 1.doc');
Insert Into @TableB Values (2, 1, 'Document 2.doc');
Insert Into @TableB Values (2, 2, 'Document 2.doc');
Insert Into @TableB Values (3, 5, 'Document 3.doc');
I've been thinking how to resolve the following logic:
(Word='A' And Word='B' And Word='C') Or Word='E'
Which should yield as result Document 1.doc (because although it has more than 3 words it contains all the 3 words I'm looking for) And Document 3.doc (because or operator)
Any ideas?
sql-server tsql edited Jan 19 at 17:08
Jason Aller
3,06192932
asked Jan 18 at 21:51
PedroPedro
506
add a comment |
1
I have two tables named Table A and Table B. Table A is a list of words and Table B is a list of documents related to those words. I need to do a search where I need to retrieve which documents have all the words I look for or some of them.
The tables structures and data example is as follow:
Declare @TableA Table (IdWord Int, Word Varchar(100))
Declare @TableB Table (IdProcess Int, IdWord Int, Document Varchar(100))
Insert Into @TableA Values (1, 'A');
Insert Into @TableA Values (2, 'B');
Insert Into @TableA Values (3, 'C');
Insert Into @TableA Values (4, 'D');
Insert Into @TableA Values (5, 'E');
Insert Into @TableB Values (1, 1, 'Document 1.doc');
Insert Into @TableB Values (1, 2, 'Document 1.doc');
Insert Into @TableB Values (1, 3, 'Document 1.doc');
Insert Into @TableB Values (1, 4, 'Document 1.doc');
Insert Into @TableB Values (2, 1, 'Document 2.doc');
Insert Into @TableB Values (2, 2, 'Document 2.doc');
Insert Into @TableB Values (3, 5, 'Document 3.doc');
I've been thinking how to resolve the following logic:
(Word='A' And Word='B' And Word='C') Or Word='E'
Which should yield as result Document 1.doc (because although it has more than 3 words it contains all the 3 words I'm looking for) And Document 3.doc (because or operator)
Any ideas?
sql-server tsql edited Jan 19 at 17:08
Jason Aller
3,06192932
asked Jan 18 at 21:51
PedroPedro
506
add a comment |
1
1
1
I have two tables named Table A and Table B. Table A is a list of words and Table B is a list of documents related to those words. I need to do a search where I need to retrieve which documents have all the words I look for or some of them.
The tables structures and data example is as follow:
Declare @TableA Table (IdWord Int, Word Varchar(100))
Declare @TableB Table (IdProcess Int, IdWord Int, Document Varchar(100))
Insert Into @TableA Values (1, 'A');
Insert Into @TableA Values (2, 'B');
Insert Into @TableA Values (3, 'C');
Insert Into @TableA Values (4, 'D');
Insert Into @TableA Values (5, 'E');
Insert Into @TableB Values (1, 1, 'Document 1.doc');
Insert Into @TableB Values (1, 2, 'Document 1.doc');
Insert Into @TableB Values (1, 3, 'Document 1.doc');
Insert Into @TableB Values (1, 4, 'Document 1.doc');
Insert Into @TableB Values (2, 1, 'Document 2.doc');
Insert Into @TableB Values (2, 2, 'Document 2.doc');
Insert Into @TableB Values (3, 5, 'Document 3.doc');
I've been thinking how to resolve the following logic:
(Word='A' And Word='B' And Word='C') Or Word='E'
Which should yield as result Document 1.doc (because although it has more than 3 words it contains all the 3 words I'm looking for) And Document 3.doc (because or operator)
Any ideas?
sql-server tsql edited Jan 19 at 17:08
Jason Aller
3,06192932
asked Jan 18 at 21:51
PedroPedro
506
I have two tables named Table A and Table B. Table A is a list of words and Table B is a list of documents related to those words. I need to do a search where I need to retrieve which documents have all the words I look for or some of them.
The tables structures and data example is as follow:
Declare @TableA Table (IdWord Int, Word Varchar(100))
Declare @TableB Table (IdProcess Int, IdWord Int, Document Varchar(100))
Insert Into @TableA Values (1, 'A');
Insert Into @TableA Values (2, 'B');
Insert Into @TableA Values (3, 'C');
Insert Into @TableA Values (4, 'D');
Insert Into @TableA Values (5, 'E');
Insert Into @TableB Values (1, 1, 'Document 1.doc');
Insert Into @TableB Values (1, 2, 'Document 1.doc');
Insert Into @TableB Values (1, 3, 'Document 1.doc');
Insert Into @TableB Values (1, 4, 'Document 1.doc');
Insert Into @TableB Values (2, 1, 'Document 2.doc');
Insert Into @TableB Values (2, 2, 'Document 2.doc');
Insert Into @TableB Values (3, 5, 'Document 3.doc');
I've been thinking how to resolve the following logic:
(Word='A' And Word='B' And Word='C') Or Word='E'
Which should yield as result Document 1.doc (because although it has more than 3 words it contains all the 3 words I'm looking for) And Document 3.doc (because or operator)
Any ideas?
sql-server tsql sql-server tsql edited Jan 19 at 17:08
Jason Aller
3,06192932
asked Jan 18 at 21:51
PedroPedro
506
edited Jan 19 at 17:08
Jason Aller
3,06192932
asked Jan 18 at 21:51
PedroPedro
506
edited Jan 19 at 17:08
Jason Aller
3,06192932
edited Jan 19 at 17:08
Jason Aller
3,06192932
edited Jan 19 at 17:08
Jason Aller
3,06192932
3,06192932
asked Jan 18 at 21:51
PedroPedro
506
asked Jan 18 at 21:51
PedroPedro
506
asked Jan 18 at 21:51
PedroPedro
506
506
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
3
Using HAVING:
SELECT Document
FROM @TableA A
JOIN @TableB B
ON B.IdWord = A.IdWord
WHERE Word IN ('A', 'B','C', 'E')
GROUP BY Document
HAVING COUNT(DISTINCT CASE WHEN Word IN ('A','B', 'C') THEN Word END) = 3
OR COUNT(DISTINCT CASE WHEN Word IN ('E') THEN Word END) = 1
db<>fiddle demo
answered Jan 18 at 21:56
Lukasz SzozdaLukasz Szozda
80k1065105
Oh i see, nice way of handling it but in order to build the query for the having part I need to first build the query and then use the exec command, I was trying to avoid that and use pure transact sql. Keep in mind the input can be n words and n operators (just "and" and "or" though)
– Pedro
Jan 18 at 22:15
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',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54261902%2fselect-rows-that-must-contain-certain-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
Using HAVING:
SELECT Document
FROM @TableA A
JOIN @TableB B
ON B.IdWord = A.IdWord
WHERE Word IN ('A', 'B','C', 'E')
GROUP BY Document
HAVING COUNT(DISTINCT CASE WHEN Word IN ('A','B', 'C') THEN Word END) = 3
OR COUNT(DISTINCT CASE WHEN Word IN ('E') THEN Word END) = 1
db<>fiddle demo
answered Jan 18 at 21:56
Lukasz SzozdaLukasz Szozda
80k1065105
Oh i see, nice way of handling it but in order to build the query for the having part I need to first build the query and then use the exec command, I was trying to avoid that and use pure transact sql. Keep in mind the input can be n words and n operators (just "and" and "or" though)
– Pedro
Jan 18 at 22:15
add a comment |
3
Using HAVING:
SELECT Document
FROM @TableA A
JOIN @TableB B
ON B.IdWord = A.IdWord
WHERE Word IN ('A', 'B','C', 'E')
GROUP BY Document
HAVING COUNT(DISTINCT CASE WHEN Word IN ('A','B', 'C') THEN Word END) = 3
OR COUNT(DISTINCT CASE WHEN Word IN ('E') THEN Word END) = 1
db<>fiddle demo
answered Jan 18 at 21:56
Lukasz SzozdaLukasz Szozda
80k1065105
Oh i see, nice way of handling it but in order to build the query for the having part I need to first build the query and then use the exec command, I was trying to avoid that and use pure transact sql. Keep in mind the input can be n words and n operators (just "and" and "or" though)
– Pedro
Jan 18 at 22:15
add a comment |
3
3
3
Using HAVING:
SELECT Document
FROM @TableA A
JOIN @TableB B
ON B.IdWord = A.IdWord
WHERE Word IN ('A', 'B','C', 'E')
GROUP BY Document
HAVING COUNT(DISTINCT CASE WHEN Word IN ('A','B', 'C') THEN Word END) = 3
OR COUNT(DISTINCT CASE WHEN Word IN ('E') THEN Word END) = 1
db<>fiddle demo
answered Jan 18 at 21:56
Lukasz SzozdaLukasz Szozda
80k1065105
Using HAVING:
SELECT Document
FROM @TableA A
JOIN @TableB B
ON B.IdWord = A.IdWord
WHERE Word IN ('A', 'B','C', 'E')
GROUP BY Document
HAVING COUNT(DISTINCT CASE WHEN Word IN ('A','B', 'C') THEN Word END) = 3
OR COUNT(DISTINCT CASE WHEN Word IN ('E') THEN Word END) = 1
db<>fiddle demo
answered Jan 18 at 21:56
Lukasz SzozdaLukasz Szozda
80k1065105
answered Jan 18 at 21:56
Lukasz SzozdaLukasz Szozda
80k1065105
answered Jan 18 at 21:56
Lukasz SzozdaLukasz Szozda
80k1065105
answered Jan 18 at 21:56
Lukasz SzozdaLukasz Szozda
80k1065105
80k1065105
Oh i see, nice way of handling it but in order to build the query for the having part I need to first build the query and then use the exec command, I was trying to avoid that and use pure transact sql. Keep in mind the input can be n words and n operators (just "and" and "or" though)
– Pedro
Jan 18 at 22:15
add a comment |
Oh i see, nice way of handling it but in order to build the query for the having part I need to first build the query and then use the exec command, I was trying to avoid that and use pure transact sql. Keep in mind the input can be n words and n operators (just "and" and "or" though)
– Pedro
Jan 18 at 22:15
Oh i see, nice way of handling it but in order to build the query for the having part I need to first build the query and then use the exec command, I was trying to avoid that and use pure transact sql. Keep in mind the input can be n words and n operators (just "and" and "or" though)
– Pedro
Jan 18 at 22:15
Oh i see, nice way of handling it but in order to build the query for the having part I need to first build the query and then use the exec command, I was trying to avoid that and use pure transact sql. Keep in mind the input can be n words and n operators (just "and" and "or" though)
– Pedro
Jan 18 at 22:15
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54261902%2fselect-rows-that-must-contain-certain-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
