CODEWITHSUNDEEP
mysqlsqldatabaseselect
Anielka
Anielka

Reputation: 49

select table name to select data from

Hello (at start i wish to sorry for my bad english)

I have two tables: tbl1 and tbl2; both have same structure but dataset in them if from different sources and i can't mix them.

in another table tbl3 i have dataid and datasource. what i wish to do is to select data from table linked in source.

pseudocode i try to produce:

SELECT
    tbl3.dataid,
    tbl3.datasource,
    SEL_TBL.important_data,
    SEL_TBL.another_thing,
    SEL_TBL.something_completly_different
FROM
    tbl3
LEFT JOIN
   ( SWITCH tbl3.datasource
        CASE  'tbl1':
           tbl1 AS  >> SEL_TBL
        CASE 'tbl2':
           tbl2 AS  >> SEL_TBL
   )
ON
    SEL_TBL.dataid = tbl3.dataid

i need result that contains: important_data, another_thing and of course something_completly_different from table selected in "switch statment".

what works right now :

SELECT 
    tbl3.dataid,
    tbl3.datasource,
   (
 CASE
    WHEN tbl3.datasource ='tbl1' 
    THEN
        tbl1.important_data
  ELSE
        tbl2.important_data
  END
   ) important_data
FROM
    tbl3
LEFT JOIN
    tbl2
ON
    tbl2.dataid = tbl3.dataid
LEFT JOIN
    tbl1
ON
    tbl1.dataid = tbl3.dataid

in results of this query i got dataid, datasource and imporatn_data. I can ofcourse repeat whole case block for every single field but perhaps there is more civilized method.

oh and one more thing: tbl1.dataid and tbl2.dataid can get the same value (that's why i can't mix tables)

Upvotes: 1

Views: 173

Answers (1)

Hogan
Hogan

Reputation: 70538

The best solution is to use a left join and include the join requirements.
Then coalesce gives you your results for each field.
Since this is a standard practice for star data models SQL optimizers will make it run quite fast.

SELECT 
    tbl3.dataid,
    tbl3.datasource,
    COALESCE(tbl1.important_data, tbl2.important_data)  important_data
    COALESCE(tbl1.another_thing, tbl2.another_thing) another_thing,
    COALESCE(tbl1.something_completly_different, tbl2.something_completly_different) something_completly_different
FROM tbl3
LEFT JOIN tbl2 ON  tbl2.dataid = tbl3.dataid AND tbl3.datasource = 'tbl2'
LEFT JOIN tbl1 ON  tbl1.dataid = tbl3.dataid AND tbl3.datasource = 'tbl1'

Upvotes: 2

Related Questions