CODEWITHSUNDEEP
mysql
Nisanth Kumar
Nisanth Kumar

Reputation: 5715

fetch from table with conditions

I have two tables hotels and company_hotels. In which hotel table consist of Id, Hotel_Name, Destination_Id and company_hotels consist of Id, Company_Id, Hotel_Id. I want to search same hotels with Company Ids 1 and 2 with Destination_Id = 10

Hotel Table

1 hotel 1 10
2 hotel 2 11
3 hotel 3 10

Company hotels

1 1 1
2 1 3
3 2 1

for the above example i need the result hotel 1 as the result

Upvotes: 0

Views: 38

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

This should do the trick

select h.Id,h.Hotel_Name
from hotel h
inner join  company_hotels ch on ch.Hotel_Id = h.Id
where 
ch.Company_Id IN (1,2)
AND h.Destination_Id = 10

UPDATE 1

From the updated comments the query should return hotels which has both company ids 1 and 2 not all the hotels having either 1 or 2

select h.Id,h.Hotel_Name
from hotels h
inner join  company_hotels ch on ch.Hotel_Id = h.Id
where 
ch.Company_Id IN (1,2)
AND h.Destination_Id = 10
group by h.Id
having count(h.Id) = 2

http://sqlfiddle.com/#!2/9025d/3

Upvotes: 2

Shaun Hare
Shaun Hare

Reputation: 3871

join on hotel_id 

Select * from company_hotels inner join hotel
on hotel.id=company_hotels.hotel_id 
where company_hotel.company_id in (1,2) 
and hotel.destination_id=10

Upvotes: 0

Related Questions