how to select data from multiple table with variable condition | MySQL

Question

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:

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:

    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

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

Thanks in advance

Do you just want to `INNER JOIN` the two tables? Or return them both separately, but in one transaction with the database? — MatBailie, May 24 '18 at 10:54
@MatBailie what i want is to get the client basic info with the latest subscription date along with sub_price and other detals. i updated the question to show the desired result — Ali, May 24 '18 at 11:06
try left join, and you need to select all field which is required either get value or not you can manage on our code. — Devsi Odedra, May 24 '18 at 11:11
@DevsiOdedra i need something like if statment in the query, since it is conditioned with the latest `max(sub_date)` client transaction, i can't find a query that wil get result if condition not valid, what i mean if client have no transaction in the detail table, the query will not select the basic data from the internetClient table — Ali, May 24 '18 at 11:16
What version of mySQL? could possibly use an analytical function — xQbert, May 24 '18 at 12:41
Nope [8.0 does](https://stackoverflow.com/questions/11806528/does-mysql-have-the-equivalent-of-oracles-analytic-functions) But we can simulate it using derived tables. — xQbert, May 24 '18 at 12:59

Roshni hegde · Answer 1 · 2018-05-24T12:55:33.227

try with left join. It will display all records from internetClient and related record from 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

this works fine, but not as i want, since this is getting all records for of the same client, what i need is to select the client with all the details in the `internetClient` and only the last record in the `internetclientDetails`, and if there are no records in the `internetClientDetails`, then only getting the record in the `internetClient` which are the basic info. thanks in advance — Ali, May 24 '18 at 11:47
I have updated the code. Check if it works. I have added order by desc — Roshni hegde, May 24 '18 at 12:24
still getting duplication in records :/, your query selects all record of same client, while it meant to only return only one record for each client with a related data if exists in the `internetclientdetails` and if not getting only basic info from `internetclient`, you effort is much appreciated, this is stopping my work flow :S — Ali, May 24 '18 at 12:37

xQbert · Accepted Answer · 2018-05-24T13:15:03.047

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.

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.

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

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.

(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.

(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)
You wanted related records from InternetClientDetail (LEFT Join InternetClientDetail ICD) while retaining teh records from InternetClient.
You ONLY wanted the most current record from InternetClientDetail (INNER JOIN InternetClientDetail mICD as a derived table getting ICID and max(date))
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.

Corrected several problems w/ the joins and some aliasing typos. Also added explanations. — xQbert, May 24 '18 at 12:53
THIS IS AWESOME, WORK LIKE A CHARM, many thanks :D, i spent all the night trying to fix it. — Ali, May 24 '18 at 13:01
Always break the problem down into components. Each step becomes easier to solve. Tackling it all at once can be complex. How do you eat an elephant? one bite at a time. Break down each aspect into it's own component and the complex becomes pretty simple. — xQbert, May 24 '18 at 13:02
Sorry about the updates I just like making it pretty incase others find it useful ;P — xQbert, May 24 '18 at 13:18

how to select data from multiple table with variable condition | MySQL

2 Answers2