running a query selecting data from one table and one of two possible other tables depending on data in the first table

Question

I'm updating a site at the moment which can potentially run a ridiculous amount of queries!

In simple form, there are 3 tables;

Updates UpdateID Files_FileID Pages_PageID UpdateDate

Pages PageID PageTitle

Files FileID FileTitle

Files_FileID in the Updates table links to FileID in the Files table Pages_PageID in the Updates table links to PageID in the Pages table

At present the code does a select query on the Updates table, then loops through those results running another query for each result on either the Pages table if the Pages_PageID has a value, or on the Files table if the Files_FileID has a value. Now this might have been alright ( if poor practice ) when the site was originally done about 15 years ago, but i'm just about to add nearly 2000 new files that will be listed in the updates table!

I'd like to change it ( if possible ) to run one query. Can this be done?

I can see i could split it into two queries, one for the Pages and one for the Files, but then i'd have two results both based on the UpdateDate and i couldn't display them in the correct order.

Answer 1

Presumably, you want to get the type and title of the thing being updated.

The answer to your question is that a proper join should be a big benefit. In this case, the following may do what you want:

select u.updateid, updatedate,
       (case when f.fileid is not null then 'File' else 'Page' end) as which,
       coalesce(FileTitle, PageTitle) as title
from updates u left join
     files f
     on u.files_fileid = f.fileid left join
     pages p
     on u.pages_pageid = p.pageid;

This assumes that each record only refers to one page or one file, but not both. Also, for performance, you want an index on the primary keys in each table (if they are defined as primary keys, then there is an index).