Reputation: 753
Consider two tables with following schemas
Raipur_Restaurant_List
RestID
name
where RestID is the primary key
Raipur_Restaurant_List_Dine_Types
RestID
Dine_Name
where RestID is the foreign key
referenced to Raipur_Restaurant_List table
select distinct RRL.RestID, RRL.name
from Raipur_Restaurant_List as RRL
join Raipur_Restaurant_List_Dine_Types as RRLDT
on RRL.RestID = RRLDT.RestID
where RRLDT.Dine_Name = 'HomeDelivery'
and RRL.RestID
IN (select RestID from Raipur_Restaurant_List_Dine_Types where Dine_Name ='Bakeries')
I am using above query to find those restaurants which have both HomeDelivery and Bakeries, Is there any better or efficient way to achieve this task ??
Thanks in advance
Upvotes: 1
Views: 26
Reputation: 270609
Another way to accomplish this using just the one join and no subquery instead uses the IN ()
to match both desired values, but also implements an aggregate COUNT()
and limits the result set to those aggregate groups with COUNT() = 2
, since that implies that it must have both values:
SELECT DISTINCT
RRL.RestID,
RRL.name
FROM
Raipur_Restaurant_List as RRL
JOIN Raipur_Restaurant_List_Dine_Types as RRLDT
on RRL.RestID = RRLDT.RestID
WHERE
-- Filter for both values
RRLDT.Dine_Name IN ('HomeDelivery', 'Bakeries')
-- GROUP BY is needed to apply the COUNT()
GROUP BY
RRL.RestID,
RRL.name
-- And filter the aggregate groups
-- for those having exactly two, meaning
-- both conditions were matched by the IN ()
HAVING COUNT(DISTINCT RRLDT.Dine_Name) = 2
The IN()
clause itself would return rows having only one or the other of HomeDelivery,Bakeries
in addition to both. By applying the COUNT()
, you ensure that only those which match both are returned.
If you need to add additional matches, add them to the IN ()
list, and also increment the number to compare in the HAVING
clause to be the same as the length of the IN ()
list.
The DISTINCT
keyword inside COUNT()
is only needed if it were possible for the same Dine_Name
to exist more than once per RestID
. If there will never be duplicate RestID, Dine_Name
pairs in RRLDT
, the DISTINCT
isn't needed there.
Upvotes: 2