Select all items having silmutaneously 2different values at the same column

Question

I want to run an sql query and find all the books that have type="adventure" AND type="drama".

AND does not allow searching at the same time 2 different values of the same column.

My tables are like this

Books

 Bid    Bname     Author
  1     Sql       loren
  2     Monster   Mike
  3     Minnie    Michel

 ----------
 Copies

 Cid     Bid    Type
 1        1     Science
 2        1     Teaching
 3        2     Adventure
 4        3     Romance
 5        3     Drama
 6        3     Adventure

The result I want:

Bid     Title
 3      Minnie

The tables can't change

Nenad Zivkovic · Answer 1 · 2020-04-24T22:49:40.217

2

There are several ways to do it, here is one using two exists conditions. Bottom line is that you have to check copies table twice.

SELECT * FROM books b
WHERE EXISTS 
(
   SELECT * FROM copies c1
   WHERE b.bid = c1.bid
   AND c1.type='adventure'
)
AND EXISTS 
(
   SELECT * FROM copies c2 
   WHERE b.bid = c2.bid
   AND c2.type='drama'
)

edited Apr 24 '20 at 22:49

answered Apr 24 '20 at 22:44

Nenad Zivkovic

18,221
6
42
55

@NenadZivkovic why do we have to use EXISTS two times? I tried with single Exists but got no results and same with JOIN as well. – zealous Apr 24 '20 at 23:11
SELECT b.Bid, b.Bname FROM books b WHERE EXISTS ( SELECT c1.Bid FROM copies c1 INNER JOIN copies c2 ON c1.Bid = c2.Bid WHERE c1.type='adventure' AND b.Bid = c2.Bid AND c2.type='drama' ) This query works as expected too. The only different with your query was that in the query plan this query is doing at the end right semi join and yours is doing semi join. – Chara.g Apr 26 '20 at 11:07

zealous · Answer 2 · 2020-04-26T19:09:54.800

0

You can achieve with JOIN, here is the DEMO. And you can use EXISTS as per @NenadZivkovic answer.

select
    b.Bid,
    Bname as Title
from books b

join Copies c
on b.Bid = c.Bid
and c.Type ='Drama'

join Copies c1
on c.Bid = c1.Bid
and c1.Type = 'Adventure'

group by   
    b.Bid,
    Bname

order by
    b.Bid

edited Apr 26 '20 at 19:09

answered Apr 24 '20 at 22:31

zealous

7,336
4
16
36

My db has dublicates and this query returns the books mulitple times. I added "distinct" next to select and it worked fine! It also worked fine by adding on the end group by b.bid, b.title. Do you know which query is better; – Chara.g Apr 26 '20 at 11:03
I would suggest `group by`, also updated my answer. but you can read it here more https://stackoverflow.com/questions/164319/is-there-any-difference-between-group-by-and-distinct – zealous Apr 26 '20 at 19:09

Select all items having silmutaneously 2different values at the same column

2 Answers2