尝试根据另一个值从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屋！