How can I combine two SQL queries to show result in one table when there is inheritance-CodePudding

I have the following structure User is a parent to Assignee and Submitter. A Submitter has a one to many relationship with Request. A Request is Many to Many relationship with Assignee.

I have the following 2 queries that I'd like to combine into one table:

Select r.request_number, u.first_name as Assignee
from requests r, users u
join request_assignee ra on r.id = ra.request_id
join assignee a on u.id = ra.assignee_id;

Select requests.request_number as Request_Number, users.first_name as Submitter
from requests
Join submitter on requests.submitter_id = submitter.id
Join request_assignee on requests.id = request_assignee.request_id
join users on submitter.id = users.id;

There can be more than one assignee to the request. How can I do 1 query to display results in one table?

Here is a picture that might help with the tables:

CodePudding user response：

maybe，change the column name and keep them uniform, and try this

Select r.request_number as Request_Number, u.first_name as Submitter
from requests r, users u
join request_assignee ra on r.id = ra.request_id
join assignee a on u.id = ra.assignee_id
unionall 
Select requests.request_number as Request_Number, users.first_name as Submitter
from requests
Join submitter on requests.submitter_id = submitter.id
Join request_assignee on requests.id = request_assignee.request_id
join users on submitter.id = users.id

CodePudding user response：

Assumption: Output Request Number, Submitter, Assignee.

The query could be simplified as below. The reason is ASSIGNEE and SUBMITTER are not required. USERS with alias for two different roles should be good enough.

select r.request_number,
       s.first_name as submitter,
       a.first_name as assignee
  from requests r
  join request_assignee ra
    on r.id = ra.reqeust_id
  join users s
    on r.submitter_id = s.id
  join users a
    on ra.assignee_id = a.id;

Current data model: SUBMITTER and ASSIGNEE do not provide additional information. ID column is redundant.

Suggested data model by removing SUBMITTER and ASSIGNEE table.