MySQL query select from multiple table depending on a certain condition

Question

I have two tables as follow:

table internetclient (id,full_name,location,phone_number)

table internetclientdetails (incdid,icid,date_sub, date_exp,isPaid,profile_sub)

the data in two table is as follow: client

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

client_subscription

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

note : incdid stands for internetClientDetailsId and icid stands for internetClientId

Problem I want to make a query that return client name along with all details depending on the latest client subscription date, the result should be as follow:

------------------------------------------------------------
full_name     client_id     date_sub     sub_price
------------------------------------------------------------
 Joe Amine       4           2018-03-01      50000
 Mary            5           2018-05-01      90000

What i am tring

SELECT * FROM client c LEFT JOIN client_subscription c_s on c.id=c_s.client_id UNION SELECT * FROM client c RIGHT JOIN client_subscription c_S on c.id=c_s.client_id WHERE c.sub_date=(SELECT MAX(sub_date) from client_subscription c_s INNER JOIN client c on c.id=c_s.client_id GROUP BY c_s.client_id

i have been working on it all the night. Any help is appreciated a lot.

M Khalid Junaid · Accepted Answer · 2018-05-24T06:19:23.647

1

To get client_subscription for each client you could use a self join

select c.name, a.client_id, a.date_sub, a.sub_price
from client_subscription a
join (
    select client_id, max(date_sub) date_sub
    from client_subscription
    group by client_id
) b on a.client_id = b.client_id and a.date_sub = b.date_sub
join client c on a.client_id = c.id
order by a.date_sub

Demo

Or using left join

select c.name, a.client_id, a.date_sub, a.sub_price
from client_subscription a
left join client_subscription b on a.client_id = b.client_id and a.date_sub < b.date_sub
join client c on a.client_id = c.id
where b.client_id is null
order by a.date_sub

Demo

Using your updated data set updated queries are

select c.full_name, a.icid, a.date_sub, a.sub_price
from internetclientdetails a
join (
    select icid, max(date_sub) date_sub
    from internetclientdetails
    group by icid
) b on a.icid = b.icid and a.date_sub = b.date_sub
join internetclient c on a.icid = c.id
order by a.date_sub;

select c.full_name, a.icid, a.date_sub, a.sub_price
from internetclientdetails a
left join internetclientdetails b on a.icid = b.icid and a.date_sub < b.date_sub
join internetclient c on a.icid = c.id
where b.icid is null
order by a.date_sub

Updated Demo

edited May 24 '18 at 06:19

answered May 24 '18 at 05:28

M Khalid Junaid

63,861
10
90
118

both of them returned empty results `MySQL returned an empty result set (i.e. zero rows). (Query took 0.0005 sec)` :S :S – Ali May 24 '18 at 05:43
@AlyAlAmeen you can see 2 fiddle demos added for each query using your sample data set. Can you double check you are using the right query, Or you can share the data with us on which you executes these queries there might b chance of some issue in data – M Khalid Junaid May 24 '18 at 05:46
yes i checked that demos! results are exactly as i want, i'll provide you with my real data, which is a little bit different from the example, but i have no idea why it's not working with me. should i update the question or i have to send it directly to you? – Ali May 24 '18 at 05:54
this is the query i applied `select c.full_name, a.incdid, a.date_sub, a.profile_sub from internetclientdetails a join ( select incdid, max(date_sub) date_sub from internetclientdetails group by incdid ) b on a.incdid = b.incdid and a.date_sub = b.date_sub join internetclient c on a.incdid = c.id order by a.date_sub` – Ali May 24 '18 at 05:57
Just include it in your question – M Khalid Junaid May 24 '18 at 05:58
just updated the question with the real data i have, and thanks for your time – Ali May 24 '18 at 06:17
1

@AlyAlAmeen just updated my answer with updated data and results are same i am not sure what is the real issue you have at your environment – M Khalid Junaid May 24 '18 at 06:22
i have one more question, this query only resulting when both table `internetclient` and `internerclientdetails` are filled up, i am trying to change the query condition so i can also get client data from the `internetclient` table even if there are no id or date in the `internetClientOrders`, should i Ask a new question and provide you a link for the question ? – Ali May 24 '18 at 08:34
@AlyAlAmeen Its better if you could ask a new one – M Khalid Junaid May 24 '18 at 10:31
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/171687/discussion-between-aly-al-ameen-and-m-khalid-junaid). – Ali May 24 '18 at 10:53

score 1 · Answer 2 · answered May 24 '18 at 05:33

Hi try below sample might help you.

DECLARE @tblClient AS TABLE (ID INT , Name varchar(100))
DECLARE @tblClientSub As TABLE (id INT,client_id INT,date_sub DATE,sub_price INT)

INSERT INTO @tblClient (id,Name)
VALUES 
(1,'Linda'),
(2,'Mary'),
(3,'Joe')  

INSERT INTO @tblClientSub(Id,client_id ,    date_sub ,    sub_price)
VALUES
(1,1,'2018/01/01',50),
(2,2,'2018/02/01',50),
(3,2,'2018/03/01',30),
(4,2,'2018/04/01',30),
(5,3,'2018/01/01',50),
(6,3,'2018/07/01',50),
(7,1,'2018/02/01',40)

SELECT c.Id,c.Name,cs.date_sub,cs.sub_price
FROM @tblClient c
CROSS APPLY (SELECT TOP (1)date_sub,sub_price 
             FROM @tblClientSub 
             WHERE client_id = c.Id 
             ORDER BY date_sub DESC) cs

Ryuk Lee · Answer 3 · 2018-05-24T06:30:36.997

0

Try this

SELECT c.Name, c.id , MAX(date_sub), sub_price  FROM client c LEFT JOIN client_subscription c_s on c.id=c_s.client_id
GROUP BY c.id
ORDER BY c.id ASC

edited May 24 '18 at 06:30

answered May 24 '18 at 04:48

Ryuk Lee

720
5
12

i tried it this way: `SELECT c.Name, c.id , client_id, date_sub, sub_price FROM client c LEFT JOIN client_subscription c_s on c.id=c_s.client_id GROUP BY c.id ORDER BY c.id ASC, date_sub DESC` i removed the **as** before client_id at line 1. and i didn't get the expected results. – Ali May 24 '18 at 05:15

score 0 · Answer 4 · answered May 24 '18 at 06:13

0

select c.name as 'client_name',cs.client_id,max(cs.sub_date) as 'date_sub',cs.sub_price from client c ,
client_subscription cs where cs.client_id=c.id group by cs.client_id,cs.sub_price;

answered May 24 '18 at 06:13

devesh

618
6
26

MySQL query select from multiple table depending on a certain condition

4 Answers4