SQL SELECT non-distinct values where multiple rows match [duplicate]
This question already has an answer here:
Matching all values in IN clause
2 answers
I have a junction table for many-many relationship that looks something like this:
Image_Tags:
Image_ID |. Tag_ID
4 | 5
4. | 6
4 | 7
5 | 6
5 | 7
I want create a SQL query to select an Image_ID that satisfies multiple Tag_ID requirements. For example, I want to find all Image_IDs that have both Tag_ID 5 AND 6, returning image 4 but not image 5. How would I go about this?
mysql sql
edited Dec 24 '18 at 7:05
philipxy
11.8k42353
asked Nov 26 '18 at 3:17
Sam GSam G
427
marked as duplicate by philipxy, Community♦ Nov 26 '18 at 16:59
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
Matching all values in IN clause
2 answers
I have a junction table for many-many relationship that looks something like this:
Image_Tags:
Image_ID |. Tag_ID
4 | 5
4. | 6
4 | 7
5 | 6
5 | 7
I want create a SQL query to select an Image_ID that satisfies multiple Tag_ID requirements. For example, I want to find all Image_IDs that have both Tag_ID 5 AND 6, returning image 4 but not image 5. How would I go about this?
mysql sql
edited Dec 24 '18 at 7:05
philipxy
11.8k42353
asked Nov 26 '18 at 3:17
Sam GSam G
427
marked as duplicate by philipxy, Community♦ Nov 26 '18 at 16:59
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
Hi. This is (obviously) a duplicate. (And shows no research effort & as an obvious faq it is not useful.) Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 26 '18 at 6:00
Why did you tag with relational algebra?
– philipxy
Nov 26 '18 at 6:02
add a comment |
This question already has an answer here:
Matching all values in IN clause
2 answers
I have a junction table for many-many relationship that looks something like this:
Image_Tags:
Image_ID |. Tag_ID
4 | 5
4. | 6
4 | 7
5 | 6
5 | 7
I want create a SQL query to select an Image_ID that satisfies multiple Tag_ID requirements. For example, I want to find all Image_IDs that have both Tag_ID 5 AND 6, returning image 4 but not image 5. How would I go about this?
mysql sql
edited Dec 24 '18 at 7:05
philipxy
11.8k42353
asked Nov 26 '18 at 3:17
Sam GSam G
427
This question already has an answer here:
Matching all values in IN clause
2 answers
I have a junction table for many-many relationship that looks something like this:
Image_Tags:
Image_ID |. Tag_ID
4 | 5
4. | 6
4 | 7
5 | 6
5 | 7
I want create a SQL query to select an Image_ID that satisfies multiple Tag_ID requirements. For example, I want to find all Image_IDs that have both Tag_ID 5 AND 6, returning image 4 but not image 5. How would I go about this?
This question already has an answer here:
Matching all values in IN clause
2 answers
mysql sql
mysql sql
edited Dec 24 '18 at 7:05
philipxy
11.8k42353
asked Nov 26 '18 at 3:17
Sam GSam G
427
edited Dec 24 '18 at 7:05
philipxy
11.8k42353
asked Nov 26 '18 at 3:17
Sam GSam G
427
edited Dec 24 '18 at 7:05
philipxy
11.8k42353
edited Dec 24 '18 at 7:05
philipxy
11.8k42353
edited Dec 24 '18 at 7:05
philipxy
11.8k42353
11.8k42353
asked Nov 26 '18 at 3:17
Sam GSam G
427
asked Nov 26 '18 at 3:17
Sam GSam G
427
asked Nov 26 '18 at 3:17
Sam GSam G
427
427
marked as duplicate by philipxy, Community♦ Nov 26 '18 at 16:59
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by philipxy, Community♦ Nov 26 '18 at 16:59
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
Hi. This is (obviously) a duplicate. (And shows no research effort & as an obvious faq it is not useful.) Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 26 '18 at 6:00
Why did you tag with relational algebra?
– philipxy
Nov 26 '18 at 6:02
add a comment |
Hi. This is (obviously) a duplicate. (And shows no research effort & as an obvious faq it is not useful.) Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 26 '18 at 6:00
Why did you tag with relational algebra?
– philipxy
Nov 26 '18 at 6:02
Hi. This is (obviously) a duplicate. (And shows no research effort & as an obvious faq it is not useful.) Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 26 '18 at 6:00
Hi. This is (obviously) a duplicate. (And shows no research effort & as an obvious faq it is not useful.) Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 26 '18 at 6:00
Why did you tag with relational algebra?
– philipxy
Nov 26 '18 at 6:02
Why did you tag with relational algebra?
– philipxy
Nov 26 '18 at 6:02
add a comment |
1 Answer
1
active
oldest
votes
You can use group by and having:
select image_id
from image_tags
where tag_id in (5, 6)
group by image_id
having count(*) = 2; -- "2" is the number of tags in the IN list
answered Nov 26 '18 at 3:19
Gordon LinoffGordon Linoff
790k35314418
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use group by and having:
select image_id
from image_tags
where tag_id in (5, 6)
group by image_id
having count(*) = 2; -- "2" is the number of tags in the IN list
answered Nov 26 '18 at 3:19
Gordon LinoffGordon Linoff
790k35314418
add a comment |
You can use group by and having:
select image_id
from image_tags
where tag_id in (5, 6)
group by image_id
having count(*) = 2; -- "2" is the number of tags in the IN list
answered Nov 26 '18 at 3:19
Gordon LinoffGordon Linoff
790k35314418
add a comment |
You can use group by and having:
select image_id
from image_tags
where tag_id in (5, 6)
group by image_id
having count(*) = 2; -- "2" is the number of tags in the IN list
answered Nov 26 '18 at 3:19
Gordon LinoffGordon Linoff
790k35314418
You can use group by and having:
select image_id
from image_tags
where tag_id in (5, 6)
group by image_id
having count(*) = 2; -- "2" is the number of tags in the IN list
answered Nov 26 '18 at 3:19
Gordon LinoffGordon Linoff
790k35314418
answered Nov 26 '18 at 3:19
Gordon LinoffGordon Linoff
790k35314418
answered Nov 26 '18 at 3:19
Gordon LinoffGordon Linoff
790k35314418
answered Nov 26 '18 at 3:19
Gordon LinoffGordon Linoff
790k35314418
790k35314418
add a comment |
add a comment |
Hi. This is (obviously) a duplicate. (And shows no research effort & as an obvious faq it is not useful.) Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 26 '18 at 6:00
Why did you tag with relational algebra?
– philipxy
Nov 26 '18 at 6:02