Madhan Shah Madhan Shah - 9 months ago 26
MySQL Question

SQL Query to fetch results from a table

Many thanks for any help.

I have a following table consisting of 2 columns service_id and artist_id. Primary Key is (service_id, artist_id). I want to retrieve all the artists for a set of service ids.


Sample Table :

service_id artists_id
5 9
6 9
5 10
1 9
5 1
6 1
6 7
1 10



I tried this and it is not working as it gives all the artists who give either service id 5 or service id 6 or service id 1.


SELECT artists_id FROM `service_schedule` WHERE service_id IN (5, 6, 1)


I want artists who give service id 5 And Service Id 6 And Service Id 1. For the above sample table only artist 9 gives all the 3 services in the set (5,6,1).

So if i want to retrieve all artists who can give services with id 5 and 6 and 1. How do i write a SQL query?

Answer Source

Once try this,

select artist_id from table where service_id IN (5, 6) AND  
artist_id IN (select artist_id from table group by artist_id having count(*) > 1);

I am fetching all artist_id which has count greater than 1 and must be service_id with 5 and 6.

I hope this will help.

EDIT

select artists_id from Sample where service_id IN (5, 6, 1)  
AND  
artists_id IN (select artists_id from Sample group by artists_id  
having count(*) > 2) group by artists_id;

You can get, service_ids like, 5,6 or 5,6,1, you just need to take count of service ids and then keep that value - 1 in place of 2