尝试根据另一个值从5个不同的表中获取一个值 [英] Trying to get a value from 5 different tables based on another value
问题描述
我正在尝试根据usertype
值从5个不同的表中获取date
值,并将其显示在DATATABLES table
上.
I'm trying to get the date
value from 5 different tables based on the usertype
value and display it on DATATABLES table
.
例如:-如果usertype
的值是2,则我想从表table_two
中获取date
的值.
For example :- if usertype
value is 2 than I want to get the date
value from table table_two
.
这是到目前为止我得到的:
Here is what I have got so far :
SELECT
CASE
WHEN tm.usertype = 1 THEN type_1.date
WHEN tm.usertype = 2 THEN type_2.date
WHEN tm.usertype = 3 THEN type_3.date
WHEN tm.usertype = 4 THEN type_4.date
WHEN tm.usertype = 5 THEN type_5.date
END,
tm.id,
tm.usertype
FROM table_main tm
LEFT JOIN table_one type_1 ON tm.id=type_1.uid
LEFT JOIN table_two type_2 ON tm.id=type_2.uid
LEFT JOIN table_three type_3 ON tm.id=type_3.uid
LEFT JOIN table_four type_4 ON tm.id=type_4.uid
LEFT JOIN table_five type_5 ON tm.id=type_5.uid
我从DATATABLES中得到的错误是:-
The error I'm getting from DATATABLES is:-
DataTables警告:表id = example-发生SQL错误:SQLSTATE [42S22]:找不到列:1054字段列表"中的未知列日期"
DataTables warning: table id=example - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'date' in 'field list'
推荐答案
您的问题显然是由没有您期望名称的列引起的.但是,您的查询可能仍未返回所需的内容,因为ID值可以(至少在理论上是)可以在表之间共享.
Your question is clearly caused by a column not having the name you expect. However, your query may still not return what you want, because id values could (at least in theory) be shared among tables.
最好将条件逻辑移到on
子句:
It is better to move the conditional logic to the on
clause:
SELECT COALESCE(type_1.date, type_2.date, type_3.date, type_4.date, type_5.date)
tm.id, tm.usertype
FROM table_main tm LEFT JOIN
table_one type_1
ON tm.id = type_1.uid AND tm.usertype = 1 LEFT JOIN
table_two type_2
ON tm.id = type_2.uid AND tm.usertype = 2 LEFT JOIN
table_three type_3
ON tm.id = type_3.uid AND tm.usertype = 3 LEFT JOIN
table_four type_4
ON tm.id = type_4.uid AND tm.usertype = 4 LEFT JOIN
table_five type_5
ON tm.id = type_5.uid AND tm.usertype = 5 ;
这不会解决您遇到的特定错误.为此,您需要修复列名.
This won't fix the specific error you have. For that, you need to fix the column names.
这篇关于尝试根据另一个值从5个不同的表中获取一个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!