如何从多个表中选择具有可变条件的数据| MySQL的

I have two tables in the datbase to store client basic info (name, location, phone number) and another table to store client related transactions (date_sub, profile_sub,isPaid,date_exp,client_id) and i have an html table to view the client basic info and transaction if are available, my problem that i can't get a query to select the client info from table internetClient and from internetclientDetails at the same time, because query is only resulting when client have trans in the detail table. the two table fields are as follow:

我在数据库中有两个表来存储客户端基本信息（名称，位置，电话号码）和另一个存储客户端相关事务的表（date_sub，profile_sub，isPaid，date_exp，client_id），我有一个html表来查看客户端基本信息和事务如果可用，我的问题是我无法同时从表internetClient和internetclientDetails中选择客户端信息的查询，因为查询仅在客户端在详细信息表中具有trans时生成。两个表字段如下：

internetClient

--------------------------------------------------------
id         full_name       location    phone_number
-------------------------------------------------------
4         Joe Amine         beirut       03776132
5         Mariam zoue       beirut       03556133

and

和

internetclientdetails 

--------------------------------------------------------------------------
incdid   icid      date_sub      date_exp      isPaid      sub_price
----------------------------------------------------------------------------
  6        4      2018-01-01     2018-01-30      0           2000
  7        5      2017-01-01     2017-01-30      0           1000
  8        4      2018-03-01     2018-03-30      1           50000
  9        5      2018-05-01     2019-05-30      1           90000

// incdid > internetClientDetailsId
// icid> internetClientId

if client have trans in orderdetails, the query should return value like that:

如果客户端具有trans in orderdetails，则查询应返回如下值：

    client_id    full_name           date_sub     date_exp      isPaid    sub_price
-------------------------------------------------------------------------------------
       4          Joe Amine          2018-03-01     2018-03-30      1           50000
       5           Mariam zoue       2018-05-01     2019-05-30      1           90000

else if the client has no id in internetOrederDetails

否则，如果客户端在internetOrederDetails中没有id

    --------------------------------------------------------
    icid      full_name       location    phone_number
    -------------------------------------------------------
    4         Joe Amine         beirut       03776132
    5         Mariam zoue       beirut       0355613

Thanks in advance

提前致谢

2 个解决方案

#1

SUMMARY

概要

We generate a dataset containing just the ICID and max(date_sub) (alias:ICDi) We join this to the InternetClientDetails (ICD) to obtain just the max date record per client. Then left join this to the IC record; ensuring we keep all InternetClient(IC) records; and only show the related max Detail Record.

我们生成一个仅包含ICID和max（date_sub）的数据集（别名：ICDi）我们将此连接到InternetClientDetails（ICD）以获取每个客户端的最大日期记录。然后将其加入IC记录;确保我们保留所有InternetClient（IC）记录;并仅显示相关的最大详细记录。

The below approach should work in most mySQL versions. It does not use an analytic which we could use to get the max date instead of the derived table provided the MySQL version you use supported it.

以下方法适用于大多数mySQL版本。如果您使用的MySQL版本支持它，它不会使用我们可以用来获取最大日期而不是派生表的分析。

FINAL ANSWER:

最终答复：

SELECT IC.id
     , IC.full_name
     , IC.location
     , IC.phone_number
     , ICD.icid
     , ICD.incdid
     , ICD.date_sub
     , ICD.date_exp
     , ICD.isPaid
     , ICD.sub_price 
FROM internetClient IC
LEFT JOIN (SELECT ICDi.*
           FROM internetclientdetails ICDi
           INNER JOIN (SELECT max(date_sub) MaxDateSub, ICID 
                       FROM internetclientdetails 
                       GROUP BY ICID) mICD
              ON ICDi.ICID = mICD.ICID
             AND ICDi.Date_Sub = mICD.MaxDateSub
           ) ICD
  on IC.id=ICD.icid 
ORDER BY ICD.incdid desc

BREAKDOWN / EXPLANATION

BREAKDOWN / EXPLANATION

The below gives us a subset of max(date_Sub) for each ICID in clientDetails. We need to so we can filter out all the records which are not the max date per clientID.

下面给出了clientDetails中每个ICID的max（date_Sub）子集。我们需要这样我们可以过滤掉所有不是每个clientID的最大日期的记录。

(SELECT max(date_sub) MaxDateSub, ICID 
 FROM internetclientdetails 
 GROUP BY ICID) mICD

Using that set we join to the details on the Client_ID's and the max date to eliminate all but the most recent detail for each client. We do this because we need the other detail attributes. This could be done using a join or exists. I prefer the join approach as it seems more explicit to me.

使用该集合，我们将加入Client_ID和最大日期的详细信息，以消除每个客户端的所有细节。我们这样做是因为我们需要其他细节属性。这可以使用join或exists来完成。我更喜欢连接方法，因为它对我来说似乎更明确。

(SELECT ICDi.*
 FROM internetclientdetails ICDi
 INNER JOIN (SELECT max(date_sub) MaxDateSub, ICID 
             FROM internetclientdetails 
             GROUP BY ICID) mICD
    ON ICDi.ICID = mICD.ICID
   AND ICDi.Date_Sub = mICD.MaxDateSub
 ) ICD

Finally the full query joins the client to the detail keeping client even if there is no detail using a left join.

最后，即使没有使用左连接的详细信息，完整查询也会将客户端连接到详细信息保留客户端。

COMPONENTS:

组件：

You wanted all records from InternetClient (FROM internetClient IC)
你想要所有来自InternetClient的记录（FROM internetClient IC）
You wanted related records from InternetClientDetail (LEFT Join InternetClientDetail ICD) while retaining teh records from InternetClient.
您希望从InternetClientDetail（LEFT加入InternetClientDetail ICD）获取相关记录，同时保留InternetClient中的记录。
You ONLY wanted the most current record from InternetClientDetail (INNER JOIN InternetClientDetail mICD as a derived table getting ICID and max(date))
您只需要InternetClientDetail中的最新记录（INNER JOIN InternetClientDetail mICD作为派生表获取ICID和max（日期））
Total record count should = total record count in InternetClient which means all relationships must be a 1:1o on the table joins -- one-to-one Optional.
总记录数应该= InternetClient中的总记录数，这意味着表连接上的所有关系必须是1：1o - 一对一可选。

#2

try with left join. It will display all records from internetClient and related record from internetclientdetails

尝试使用左连接。它将显示来自internetClient的所有记录以及来自internetclientdetails的相关记录

Select internetClient.id, internetClient.full_name
     , internetClient.location, internetClient.phone_number
     , internetclientdetails.incdid, internetclientdetails.icid
     , internetclientdetails.date_sub, internetclientdetails.date_exp
     , internetclientdetails.isPaid, internetclientdetails.sub_price 
from internetClient 
left join internetclientdetails 
  on internetClient.id=internetclientdetails.icid group by internetclientdetails.icid order by internetclientdetails.incdid desc

if you want to get records of, only paid clients then you can try the following

如果你想获得记录，只有付费客户，那么你可以尝试以下

Select internetClient.id, internetClient.full_name
     , internetClient.location, internetClient.phone_number
     , internetclientdetails.icid, internetclientdetails.incdid
     , internetclientdetails.date_sub, internetclientdetails.date_exp
     , internetclientdetails.isPaid, internetclientdetails.sub_price 
from internetClient 
left join internetclientdetails 
  on internetClient.id=internetclientdetails.icid 
 and internetclientdetails.isPaid=1 group by internetclientdetails.icid
order by internetclientdetails.incdid desc

#1