1

Assume we have tables similar to the following:

CREATE TABLE Base 
(
    ID int,
    Type varchar(20),
    Col1 varchar(255),
    Col2 varchar(255),
    AddedAt datetime2
);

CREATE TABLE Sub1 
(
    BaseID int,
    Col3 varchar(255),
    Col4 varchar(255),
    Col5 varchar(255)
)

CREATE TABLE Sub2
(
    BaseID int,
    Col5 varchar(255), # Note here, this has the same column name as in Sub1
    Col6 varchar(255),
    Col7 varchar(255)
)

How could you structure a query that could:

  1. Search on a number of the different columns across the tables
  2. Do the above whilst also being able to paginate over them, potentially ordering by AddedAt.

The goal of the query would be to say display a page to the user that shows these all as one list, even though they are different types. Ideally you could just run multiple queries but this then causes issues with the paging.

I have already attempted and got working some queries, but they don't feel very "nice".

The first one something like the following.

WITH Sub1Cte AS
(
    SELECT * 
    FROM Sub1
    WHERE Col5 = @someVar
),
WITH Sub2Cte AS
(
    SELECT *
    FROM Sub2
    WHERE Col5 = @someVar
)
SELECT b.*, s1c.*, s2c.*
FROM Base b
LEFT JOIN Sub1Cte s1c ON s1c.BaseID = b.ID
LEFT JOIN Sub1Cte s2c ON s2c.BaseID = b.ID
WHERE 
    s1c.BaseID IS NOT NULL 
    AND s2c.BaseID IS NOT NULL
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Another option would potentially be doing some sort of auto-generated UNION ALL but it was quite messy when I wrote it out.

I'm not against editing the table structure if they are fundamentally wrong, everything is on the table.

2
  • What’s not “nice” about your first query? Looks like a fine way to write it.
    – dwhitemv
    Oct 5, 2021 at 19:36
  • @dwhitemv I'm pretty much a novice when it comes to good SQL practices, especially with more complicated scenarios like the one I outlined. To me it felt very hacky but it might genuinely be the right way to do it. If that's so, then I guess I'll continue doing what I am doing.
    – deadwards
    Oct 6, 2021 at 8:16

1 Answer 1

1
+50

I think your query is fine. Let us know if you run into any particular issue with it.

Other observations:

  • Your question title mentions table inheritance but your table definitions don't create a parent/child relationship.
  • If there is any way you can rename the colliding Col5, it will save you a lot of trouble down the road.
  • Use the Index, Luke provides some guidance on pagination strategies. Its an advanced topic, and may not be worth the effort if your result sets are small, but is good to know for the future.
1
  • Thank you very much for that link. We did know we were doing pagination wrong but having not heard of key set pagination I didn't know the right terms to show for. Gives me lots to think about.
    – deadwards
    Oct 8, 2021 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.