CODEWITHSUNDEEP
mysqlrowrecordsmultiple-records
livinzlife
livinzlife

Reputation: 873

MySQL single row with multiple records

I have a table with contains multiple rows that define "amenities" for a particular resort. I need to return the resortID if there are rows containing whatever "amenOptionID" I define. My issue comes in where I'm looking to see if a resort has two or more amenities. For example:

I want to return resortIDs that have BOTH amenOptionID 1 AND 4. I also do not want duplicate resortIDs. Refer to the image for the table structure. Thanks in advance for any help.

Table Structure

Upvotes: 0

Views: 765

Answers (2)

James C
James C

Reputation: 14149

I think this will work...

SELECT resortID, COUNT(*) AS theCount FROM myTable WHERE amenOptionID IN(1,4) GROUP BY resortId HAVING theCount=2;

Upvotes: 0

alex
alex

Reputation: 490283

  SELECT `resortID`
   WHERE `amenOptionID`
         IN (1, 4) 
GROUP BY `resortID`
  HAVING COUNT(*) = 2

Upvotes: 2

Related Questions