CODEWITHSUNDEEP
sqlsql-server
Joeseph Schmoe
Joeseph Schmoe

Reputation: 312

Get data from one of several tables SQL

Currently we have 4 tables containing information about a particular record:

All records contain the following data:

record = {
   param1?: string;
   param2?: number;
   param3?: boolean;
}

However, depending on which table the record came from, we may have additional information, such as a timestamp, a signature of the inserter, a counter of some sort, etc. which could be stored in 1 additional column or several. (in the example below, I've shown one extra param for each table for simplicity)

Right now, if we want to search for all information about a specific record across all four tables, we have a large IF/ELSE block with very WET code, such as:

PROCEDURE dbo.myProcedure
         @search_id VARCHAR(36)
AS  
     DECLARE @source VARCHAR(3)
     SET @source = 'unk'

     -- FIGURE OUT WHICH SOURCE: 'kwn', 'upd', 'rep', 'aut'

     IF @source == 'upd'
     BEGIN
        SELECT u.param1, u.param2, u.param3, u.updOnlyParam
        FROM upd_table u
        WHERE u.search_id == @search_id
     END
     ELSE IF @source == 'kwn'
     BEGIN
        SELECT k.param1, k.param2, k.param3, k.kwnOnlyParam
        FROM kwn_table k
        WHERE k.search_id == @search_id
     END
     ELSE IF @source == 'rep'
     BEGIN
        SELECT r.param1, r.param2, r.param3, r.repOnlyParam
        FROM rep_table r
        WHERE r.search_id == @search_id
     END
     ELSE IF @source == 'aut'
     BEGIN
        SELECT a.param1, a.param2, a.param3, a.autOnlyParam
        FROM aut_table a
        WHERE a.search_id == @search_id
     END

What would be ideal is if it were possible to do something like:

PROCEDURE dbo.myProcedure
         @search_id VARCHAR(36)
AS  
     DECLARE @source VARCHAR(3)
     SET @source = 'unk'

     -- FIGURE OUT WHICH SOURCE: 'kwn', 'upd', 'rep', 'aut'

     SELECT 
        t.param1, 
        t.param2, 
        t.param3, 
        t.updOnlyParam, -- column only present when t is upd_table
        t.kwnOnlyParam, -- column only present when t is kwn_table
        t.repOnlyParam, -- column only present when t is rep_table
        t.autOnlyParam  -- column only present when t is aut_table
     FROM 
        CASE 
           WHEN @source == 'upd' THEN upd_table t
           WHEN @source == 'kwn' THEN kwn_table t
           WHEN @source == 'rep' THEN rep_table t
           ELSE aut_table t
        END
     WHERE t.search_id == @search_id

However, this doesn't work on two accounts:

  1. Putting a case statement in a FROM clause isn't supported
  2. In the SELECT clause, if a column doesn't exist, it throws an error

I am aware that the code above is non-functional and may not be 'fixable' I am wondering whether there exists a better way of querying off of multiple tables in a single statement similar to the above?

The most obvious solution is to combine all four tables to have a single table with all columns from all tables present with an extra column to identify which "case" the row pertains to. However: that is NOT what I am asking. I am asking how, keeping the tables separate, I could query off of multiple tables with similar column members, and some special columns for each table.

Thank you for your input.

Upvotes: 0

Views: 53

Answers (1)

Farshid Shekari
Farshid Shekari

Reputation: 2449

The best way, create a view and there's no need to define new columns for every table. like below:

SELECT 'aut' as source, a.param1, a.param2, a.param3, a.autOnlyParam as OnlyParam, a.search_id
FROM aut_table a
 union all
SELECT 'rep' as source, r.param1, r.param2, r.param3, r.repOnlyParam as OnlyParam, r.search_id
FROM rep_table r
union all
SELECT 'kwn' as source, k.param1, k.param2, k.param3, k.kwnOnlyParam as OnlyParam, k.search_id
FROM kwn_table k
union all
SELECT 'upd' as source, u.param1, u.param2, u.param3, u.updOnlyParam as OnlyParam, u.search_id
  FROM upd_table u

after creating this view, you just need to use this view below:

  SELECT k.param1, k.param2, k.param3, k.OnlyParam
     FROM your_named_view_name k
    WHERE k.search_id = @search_id and source = @source

Upvotes: 1

Related Questions