5

I fear the answer will be a straight 'no', but I was wondering if it's possible to do something such as the following in MySQL:

SELECT (title||label||name) FROM table

i.e Select a single column, which may be called title, label or name, from table.

Reason being: the query will be dynamically generated where table is known but (due to reasons beyond my control) there is no consistent naming convention in the other tables.

HelloPablo
  • 615
  • 1
  • 7
  • 22
  • How do you know which field you want? Or do you just want to select the whichever one exists? Might this help: http://stackoverflow.com/a/9086503 – gen_Eric Jul 10 '13 at 14:10
  • I'd like to select whichever exists (if more than one exists, then the first match), or if doesn't exist to fail gracefully. I guess I could do a `DESCRIBE` and check that way in PHP, but would prefer a single query. – HelloPablo Jul 10 '13 at 14:12
  • 1
    How is the query being generated? Can you just make a lookup table? Like `if(table === 'books'){ field = 'title' }`? – gen_Eric Jul 10 '13 at 14:14
  • Sadly, all I can know for sure is the table name and that the columns will be A, B or C. I don't know what table has which column, unfortunately. – HelloPablo Jul 10 '13 at 14:23
  • It's not like they are changing, couldn't you just look in the database manually, then hard-code a mapping between table and column? Because, other than running a query for each and seeing which doesn't fail, I can't think of another solution. – gen_Eric Jul 10 '13 at 14:25
  • This particular implementation is part of a framework/library so will be used on many databases and writing a config file for each app will defeat the purpose. Thanks for your thoughts though - it might just not be possible! – HelloPablo Jul 10 '13 at 14:26
  • Well, you could use `DESCRIBE` before you generate the query, so you know which field to use. – gen_Eric Jul 10 '13 at 14:29
  • 5
    This sounds like a disaster in the making – Strawberry Jul 10 '13 at 14:33
  • Yeah, I'll probably just do that. Thanks again, very helpful. – HelloPablo Jul 10 '13 at 14:33

4 Answers4

5

It does the trick.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='YOUR_TABLE_NAME' AND COLUMN_NAME IN ('name', 'label', 'title') into @colname;
SET @table = 'YOUR_TABLE_NAME';
SET @query = CONCAT('SELECT ',@colname,' FROM ', @table);

PREPARE stmt FROM @query;
EXECUTE stmt;

Inspiration here : Dynamic conversion of string into column name. MySQL

Community
  • 1
  • 1
kmas
  • 6,401
  • 13
  • 40
  • 62
  • Yes, it does indeed do the trick! I've opted for a change in logic but will accept this answer as it answers the question at hand. Thanks. – HelloPablo Jul 10 '13 at 14:48
  • Thanks. For many columns `SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ', ') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='YOUR_TABLE_NAME' AND COLUMN_NAME LIKE '%name%' INTO @colnames;` – araslanov_e Sep 13 '19 at 06:46
  • FIX. `SELECT GROUP_CONCAT(DISTINCT(COLUMN_NAME) SEPARATOR ', ') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='YOUR_TABLE_NAME' AND COLUMN_NAME LIKE '%name%' INTO @colnames;` – araslanov_e Sep 13 '19 at 07:00
4

I completely agree with @Strawberry's comment that "this sounds like a disaster in the making"—it's a very poor design strategy and I strongly recommend finding an alternative solution. However, it's an interesting challenge… this is my solution:

SELECT COALESCE(title,label,name) col FROM `table` NATURAL LEFT JOIN (
  SELECT NULL title, NULL label, NULL name
) t WHERE COALESCE(title,label,name) IS NOT NULL

See it on sqlfiddle.

Note that if the record data is NULL, no record will be returned.

Community
  • 1
  • 1
eggyal
  • 122,705
  • 18
  • 212
  • 237
-1

Write the query statement dynamically using the programming language. Then execute the database function using that query. Problem solved!

Shadman Fatin
  • 27
  • 3
-2

select student_id, aq.question, case aq.question_id when 1 then ans_question_1 when 2 then ans_question_2 end Answer from answers

Ansu
  • 1
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 17 '22 at 01:30