ccices ccices - 1 month ago 9
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.

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

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

Details_Event_tbl
EventID (FK)
DetailsID (FK)

Details_Descrip_tbl
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

As an example of join operations:

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