I have 2 Tables like below:

Person

Documents

I want to query with the below results

CodePudding user response：

I understand that this query is a bit messy. This should give you what you are expecting:

SELECT pName,
pAge,
pDocName,
Papers,
pFulfilled
  FROM (
SELECT 
p.`Name` AS pName
, p.`Age` AS pAge
,'' AS pDocName
,'' AS Papers
,'' AS pFulfilled
, p.`Name` AS sortName
,1 AS sortOrder 
FROM `person` p 
WHERE p.`Name` IN (SELECT d.`Name` FROM `Documents` d)
UNION ALL
SELECT 
''
,''
, d.`DocName`
, d.`Papers`
, d.`Fulfilled`
, d.`Name`,
2 AS sortOrder FROM `Documents` d )a
ORDER BY a.sortName, a.sortOrder, a.pDocName

CodePudding user response：

To get results what you whant, you can use the UNION ALL operator, which allows you to combine two or more result sets of queries into a single result set. But you must follow:

• First, the number and the orders of columns in all SELECT statements must be the same
• Second, the data types of columns must be the same or compatible

To satisfy both restrictions, you can include NULL values ​​for result columns that do not exist in the tables.

So the query would be like this

SELECT
    *
FROM (    
    SELECT
        *,
        NULL AS DocName,
        NULL AS Papers,
        NULL AS Fulfilled
    FROM Person
    UNION ALL
    SELECT
        Name,
        NULL AS Age,
        NULL AS Department,
        DocName,
        Papers,
        Fulfilled
    FROM Documents
) data
WHERE Name IN (SELECT Name FROM Documents)
ORDER BY Name