CareerCup

select g.id from genre g where g.genre='Action'
INTERSECT
select g.id from genre g where g.genre='Comedy'

Why are you making it complex...it is just

select g.id from gener g where g.genre in ("Action", "Comedy");

select id from Genre where Genre ='Action' and id in
(select id from Genre where Genre='Comedy');

(Not sure of this:)

select distinct a.id from genre a, genre b
where a.genre = "action" and b.genre = "comedy" and a.id = b.id;

Will be very inefficient, however! The distinct is because it's a bad query, it will come up with a full cross-product.

--SELF JOIN---
SELECT G1.ID FROM GENRE G1
JOIN GENRE G2 ON G1.ID = G2.ID
WHERE G1.ID = 'ACTION' AND G2.ID = 'COMEDY';

For clarity on why a simple where with or wont work.
20 Action
21 Action
22 Comedy
21 Comedy
Supposed to return 21 as it is both Action and Comedy.

If the nested query in the loop sounds confusing, albeit something longer here
select g.id
from genre g, genre x
where g.id = x.id and
((g.Genre = 'Action' and x.Genre='Comedy') or (g.Genre='Comedy' and x.Genre='Action'))

; with ctetst
as
(
select id, 
genre, 
DENSE_RANK() over (partition by id order by genre) as i
from genre)

select id
from ctetst
group by id, i
having i > 1

SELECT g.Id FROM Genre AS g
WHERE g.Genre = 'Action' OR g.Genre = 'Comedy'
GROUP BY g.Id HAVING COUNT(g.Id) = 2;

This question is ambiguous. What is the primary key of the table?

SELECT ID
FROM GENRE
WHERE GENRE IN ('ACTION','COMEDY')
GROUP BY ID
HAVING COUNT(DISTINCT(GENRE)) > 1

select id from genre
where genre in ('action', 'comedy');

select id,count(distinct gener)
from xxtest1
where gener in ('Action','Comedy')
group by id
having count(distinct gener) > 1;

SELECT ID FROM #GENER WHERE GENER IN('ACTION','COMEDY') GROUP BY ID HAVING COUNT(DISTINCT GENER)>1

--simple answer
SELECT ID FROM #GENER WHERE GENER IN('ACTION','COMEDY') GROUP BY ID HAVING COUNT(DISTINCT GENER)>1

I would suggest to use the self join, because intersect doesn't work in mySQL and EXISTS will give you all rows as the output.

SELECT A.id
FROM Genre AS A JOIN Genre AS B ON A.id = B.id
WHERE A.genre = "Action" AND B.genre = "Comedy";

select id from Genre where Genre ='Action' and id in
(select id from Genre where Genre='Comedy');

Ans a)Select distinct Id from Genre
where genre in (select genre from genre where genre = 'Action' and 'Comdey')

Ans b) select distinct Id from Genre
where genre = 'Action' and 'Comedy'

Select Id from Genre
where genre = 'action' OR genre = 'comedy'

Taken from w3 schools

Example:

SELECT * FROM Customers
WHERE City='Berlin'
OR City='München';

Solution:

SELECT Id FROM Genre
WHERE Genre='Action'
OR Genre='Comedy';