Querying tables with a zero-to-many relationship

Question

I have an application that uses a MySQL database to store, asking other things, meetings and projects. A project can have zero-to-many meetings, and a meeting can have zero-to-many projects. To assist with linking meetings to a project, I'm trying to write a single clean query that searches across all of these tables and their relations (e.g. projects have tasks, meetings have attendees)

Here's an idea of the design:

project - id - reference - description - type

project_meetings - id - project_id - meeting_id - created_at - created_by

meeting - id - reference

meeting_attendee - id - meeting_id - person_id

person - id - name

project_tasks - id - project_id - task_type_id - content

task_types - id - task_title

The simplest idea I had was using LEFT JOINs, but I found it removes the results from the tables with zero related objects. The two ways that work are to use UNIONs, subqueries, or a really horrible WHERE-AND-OR clause with brackets for Africa.

Can anyone suggest a better query pattern for this design, or a better design?

`LEFT JOIN` should include the results with no related objects. That's the difference between `INNER JOIN` and `LEFT JOIN`. You must not be doing it correctly. Since you haven't posted your query, it's impossible to tell what you did wrong. — Barmar, Apr 21 '18 at 04:47
But I'll bet this answers it: https://stackoverflow.com/questions/47449631/return-default-result-for-in-value-regardless/47449788#47449788 — Barmar, Apr 21 '18 at 04:48
You may want to look into FULL JOIN since you seem to want both the projects with no meetings and the meetings with no projects. — Walter Mitty, Apr 21 '18 at 10:39
More details - it's mysql so no full joins possible, and i will post the query tomorrow... — Ben E., Apr 21 '18 at 10:41

score 0 · Answer 1 · answered May 05 '18 at 03:46

A project can have zero-to-many meetings

-->

CREATE TABLE Projects (
    id ...

CREATE TABLE Meetings (
    id ...
    project_id ... -- this is all you need for 0:many (or 1:many)

Your sqlfiddle show a "many-to-many" relationship between Products and Meetings, not "zero-to-many". As such, it is inefficient. See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

When JOINing the two tables, you may need to use LEFT JOIN.

a single clean query that searches across all of these tables

Give an example of the output desired.

Querying tables with a zero-to-many relationship

1 Answers1