Help with Database logical query result (task assigned to user or team)

Hi everybody,
I need help to set a query correctly:

I have 4 tables

  • task: task_id, status, team_assigned_boolean (0/1), team_id (reference), user_id (reference), created_date,…
  • user: user_id, name, forename, email,…
  • teams: team_id, team_name,…
  • members: member_id, team_id (reference), user_id (reference),…

(no subtables)

A task can be assigned to a user, to a team or unasigned.
A user can be member of none or every teams.

How should I set my query(ies) to get a unique list (repeat) of tasks assigned to a unique user_id OR assigned to a team is member of?

Join, join set, inner, left, right… I can’t find the way to do that.
Many thanks.

Perhaps a simple logic change will simplify this.
At present the issue is the need to effectively perform two searches and join the results ( a custom query with a union would perhaps be the solution) however if you were to create an extra team called, for example, “not in a team” and assign all those not in a team there then the query should resolve to a simple left joins on task-> team -> members-> users removing the need to separately search on the user_id field directly

1 Like