CASE for joining sql tables

I need a help on sql database side. And i have

table 1 : ENTITY_TYPE

entity_type_id  entity_name
     1            Task
     2            Page
     3            Project
     4            Message
     5            User

and table 2 : MESSAGE , that contains message from each entity values like

message_id entity_type owner_tableid message
    1           1             12       A message on task level
    2           3             14       A message on project level

and I want select these message according to each entity type and details from its owner table using 'owner_tableid' ie a query like....

select * from MESSAGE JOIN
case entity_type when 1 then taskTable
when 2 then pageTable
when 3 then projectTable
when 4 then MessageTable
when 5 then UserTable

Which is best method to solve this issue on single procedure. Any idea ?? Now I am using IF clause for each entity...

Answers


You can't parameterise the tables involved in a query (so you can't put a table name in a variable and expect that to be used either).

One way to do it is as a chain of left joins:

select
  * /* TODO - Pick columns */
from
   MESSAGE m
      left join
   taskTable tt
      on
         m.entity_type = 1 and
         m.owner_entity_id = tt.id
      left join
   pageTable pt
      on
         m.entity_type = 2 and
         m.owner_entity_id = pt.id
      left join
   projectTable prt
      on
         m.entity_type = 3 and
         m.owner_entity_id = prt.id
      left join
   MessageTable mt
      on
         m.entity_type = 4 and
         m.owner_entity_id = mt.id
      left join
   UserTable ut
      on
         m.entity_type = 5 and
         m.owner_entity_id = ut.id

If you want values from these tables to appear in a single column in the result, use a COALESCE across all of the values, e.g.

COALESCE(tt.Value,pt.Value,prt.Value,mt.Value,ut.Value) as Value

Use Union Clause with your individual entity_type

SELECT * FROM Message
JOIN pageTable ON ....
WHERE entity_type = 1

UNION ALL
..........
entity_type = 2

UNION ALL
..........
entity_type = 3

Select  ...
From Message
    Join    (
            Select 1 As entity_type, id
            From taskTable
            Union All
            Select 2, id
            From pageTable
            Union All
            Select 3, id
            From projectTable
            Union All
            Select 4, id
            From messageTable
            Union All
            Select 5, id
            From userTable
            ) As Z
        On Z.entity_type = Message.entity_type
            And Z.id = Message.owner_tableid

If you need to return several entity_types details in one query, than UNION might help:

SELECT interesting_columns FROM Message
JOIN pageTable ON (joinPredicate)
WHERE entity_type = 1

UNION ALL

SELECT interesting_columns FROM Message
JOIN pageTable ON (joinPredicate)
WHERE entity_type = 2

-- ...

But if you only need details of certain entity_type than you original solution with IF would be much better.


Need Your Help

Remove white and unnecessary space from Chart control

c# winforms charts whitespace

I have a chart and I've added it to a panel but as you can see in the picture, the chart does not fill the whole panel. it has a lot of white space that I highlighted in red.

Lombok @Builder inheritance workaround

java lombok

Lombok @Builder doesn't work for inheritance use cases: