ccices ccices - 9 months ago 42
MySQL Question

How do I deal with a subquery that provides more than one line of results?

I am having issues trying to write a SQL Statement that finds all the events that a ID is responsible for and then list all the descriptions associated to that event.

PersonID (PK INT)
Name (Varchar eg. "John Smith")

EventID (PK INT)
SUPV_on_DutyID (FK to PersondID)
Event_Type (VarChar)

EventID (FK)
DetailsID (FK)

DetailsID (PK INT)
Details_Desc (VarChar)

Using my statement I get an error that I am returning too many rows... because it is a case that the Supv can have many Events and Events can have many details.

I need to answer "List all the events and details of events where "Jphn Smith" is responsible".

any help would be appreciated

Answer Source

As an example of join operations:

 SELECT p.personid
      , e.eventid
      , e.event_type
      , d.details_desc
   FROM `Person_tbl` p 
   JOIN `Event_tbl` e
     ON e.supv_on_dutyid = p.personid 
   JOIN `Details_Event_tbl` j
     ON j.eventid = e.eventid
   JOIN `Details_Descrip_tbl` d
     ON d.detailsid = j.detailsid
  WHERE p.personid = ? 
  ORDER BY e.eventid, d.detailsid