Link 2 MySQL tables and display data but sort by ID-CodePudding

I have two tables within the same database, one for file info (for files that have been uploaded to the server) and the other is for notes (notes that have been pinned to an event).

When the user opens a specific event it searches each table (File and Notes) to see if there has been any Files or Notes uploaded that are pinned to that event and it does this by searching for EventId in both tables.

I was to display the results in one list so I basically want the results to merge into one and I want to be able to loop through it and if ID 1 is from File then it will display something from File and if ID 2 is from Notes then it will display a note.

File Table Structure
FileID | EventId | FileName | FilePath | FileTimestamp

Notes Table Structure
NoteId | EventId | noteContents | noteTimestamp

CodePudding user response：

A simple UNION would do

SELECT * FROM (
    SELECT
        1 as isFile,
        f.FileID as id,
        CONCAT(f.FilePath, f.FileName) as content,
        f.FileTimestamp as eventTimestamp
    FROM File f WHERE f.EventId = @id
    UNION
    SELECT
        0 as isFile,
        n.NoteId as id,
        n.noteContents as content,
        n.noteTimestamp as eventTimestamp
    FROM Notes n WHERE n.EventId = @id
) as x

Just replace @id with your own variable / parameter / value

You can add an order by id after all that but I don't think both tables are using the same sequence to generate their IDs