mysql select primary record and multiple secondary records as sorted data set

Question

I have two tables: locations and events

Each location can have multiple events. I am successfully doing a single query to grab all the location and matching events, however, the events are being returned as a merged data-set with all of the location data. I need to somehow have each, and all, events, listed as sub-rows so I can properly process them later.

This query:

"select locations.*,events.* FROM locations,events WHERE locations.lid = events.lid AND locations.lid=1001"

Is returning merged data like:

data [array 1]
   0: lid: "1001"  // location info
      name: "Johns Bar"
      address1: "123 Main St"
      ...
      ...
      eventID: "1000" // event info
      eName: "Halloween Bash"
      eDate: "2018-10-31"
      ...
      ...

Is it possible to grab the specific locations.lid and all matching events.lid and have the events records listed as a subset of location data.

Something that would return like:

data [array 1]
   0: lid: "1001"
      name: "Johns Bar"
      address1: "123 Main St"
      ...
      ...
      Events: [array 5]
         0: lid: "1001"
            eventID: "1000"
            eName: "Halloween Bash"
            eDate: "2018-10-31"
            ...
            ...
         1: lid: "1001"
            eventID: "1010"
            eName: "Christmas Party"
            eDate: "2018-12-17"
            ...
            ...
         2: [lid: "1001",...]
         3: [lid: "1001",...]
         4: [lid: "1001",...]

Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Nov 20 '18 at 16:11
SQL results data in tabular manner. You will need to array operations in your application code to change into required format — Madhur Bhaiya, Nov 20 '18 at 16:12
@MadhurBhaiya - so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records? If not, then this will force me to do two queries...or is there another method? — rolinger, Nov 20 '18 at 16:30
There is not. You could do a group concat in which case you'll end up with an enormous string. SQL is not designed to code around your master detail requirements. — T Gray, Nov 20 '18 at 16:41

score 1 · Answer 1 · answered Nov 20 '18 at 16:45

so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records?

Yes, this is how a JOIN works.

If not, then this will force me to do two queries...or is there another method?

No, you don't have to run multiple queries.

You can fetch your result set as SQL returns it, with pairings of matching location and event rows. Make sure you sort by location in your SQL query.

Then as you fetch the rows of result, keep track of the "current" location in a variable in your client app. If you fetch a row and its location is the same as the location you had seen before, then ignore it.

Pseudocode:

sql = SELECT ... FROM location JOIN event ... ORDER BY location
execute sql
location = nil
while row = fetch():
  if row[location] != location:
    print location
  end if
  location = row[location] # for next time
  print event
end while

This is a common pattern for processing SQL result sets.

mysql select primary record and multiple secondary records as sorted data set

1 Answers1