MySql dynamic select

Question

Table Name: Look
FieldName: LookUp
example fieldname value : Country.CountryCode

While making a select inside table 'Look' I should dynamically split on value of the fieldname 'LookUp' and get the first value as Tablename and second value as Fieldname to do a dynamic select. I have the split function in place the problem is how to make it work in a case statement or maybe somebody has an alternative solution. currently i have this which is clearly not working

SELECT l.Id,
  case when l.lookup is not null then
      SELECT t.Id
      FROM (SPLIT_STR(l.LOOKUP,'.',1)) AS t 
      WHERE t.(SPLIT_STR(l.LOOKUP,'.',2)) = l.attValue
      LIMIT 1
  END AS attValue
FROM look as l

score 0 · Accepted Answer · edited May 23 '17 at 12:28

Don't believe it is possible to pick up the table name from a field. Does suggest that there is an issue with your database design though.

Previous similar question:-

MYSQL query using variable as table name in LEFT JOIN

If there is a limited number of related tables / fields to join on and you know them all in advance then something like the following might do it:-

SELECT l.Id, 
CASE 
    WHEN SPLIT_STR(l.LOOKUP,'.',1) = 'tableA' THEN tableA.Id 
    WHEN SPLIT_STR(l.LOOKUP,'.',1) = 'tableB' THEN tableB.Id 
    WHEN SPLIT_STR(l.LOOKUP,'.',1) = 'tableC' THEN tableC.Id 
    WHEN SPLIT_STR(l.LOOKUP,'.',1) = 'tableD' THEN tableD.Id 
    ELSE NULL
END AS SubId
FROM look as l 
LEFT OUTER JOIN tableA ON tableA.ColA = l.attValue
LEFT OUTER JOIN tableB ON tableA.ColB = l.attValue
LEFT OUTER JOIN tableC ON tableA.ColC = l.attValue
LEFT OUTER JOIN tableD ON tableA.ColD = l.attValue

Ie, join against every possible sub table and use a CASE to return the field from the one you want.

But if you are reduced to doing this then I would suggest redesigning the database at the earliest opportunity.

MySql dynamic select

1 Answers1