It’s more about to test your thinking , Even if you are not able to solve it, can you at least understand the logic required and how you go about solving such problems
I learned something from here. Thanks kind sir. Awesome content as always. Keep uploading more videos like this and maybe next time something that solves a real life business problem as well.
Hi brother how are you? we are learning so many good things about sql you are way of explanation is superb i have a small request can you please do using stored procedures we will learn that process also please
Hi mentor, it was pleasure to subscribe your channel.. I m learning sql every nook and corner of the logic..im really grateful to come across ur channel... Thank u so much.. I have a doubt.. I'm 33 .im reforming my career in data analytics...could u share ur opinion
Hey TFQ, thanks for this great explanation Let me also share you my solution for the same problem I don't know if it is good practice or not but I tried to solve it differently and it worked CREATE TABLE #temp_table ( Person_id NVARCHAR(1000) , Relative_id_1 NVARCHAR(1000) , Relative_id_2 NVARCHAR(1000) ) INSERT INTO #temp_table VALUES ('ATR-1',null,null), ('ATR-2','ATR-1',null), ('ATR-3','ATR-2',null), ('ATR-4','ATR-3',null), ('ATR-5','ATR-4',null), ('BTR-1',null,null), ('BTR-2',null,'BTR-1'), ('BTR-3',null,'BTR-2'), ('BTR-4',null,'BTR-3'), ('BTR-5',null,'BTR-4'), ('CTR-1',null,'CTR-3'), ('CTR-2','CTR-1',null), ('CTR-3',null,null), ('DTR-1','DTR-3','ETR-2'), ('DTR-2',null,null), ('DTR-3',null,null) , ('ETR-1',null,'DTR-2'), ('ETR-2',null,null), ('FTR-1',null,null), ('FTR-2',null,null), ('FTR-3',null,null), ('GTR-1','GTR-1',null), ('GTR-2','GTR-1',null), ('GTR-3','GTR-1',null), ('HTR-1','GTR-1',null), ('HTR-2','GTR-1',null), ('HTR-3','GTR-1',null), ('ITR-1',null,null), ('ITR-2','ITR-3','ITR-1'), ('ITR-3',null,null); --SELECT * FROM #temp_table CREATE TABLE #temp_data ( grp NVARCHAR(10) , family NVARCHAR(1000) ) INSERT INTO #temp_data SELECT SUBSTRING(Person_id,1,1) as grp , Person_id FROM #temp_table WHERE Person_id is not null and Relative_id_1 is not null and Relative_id_2 is not null UNION SELECT SUBSTRING(Person_id,1,1) as grp , Relative_id_1 FROM #temp_table WHERE Person_id is not null and Relative_id_1 is not null and Relative_id_2 is not null UNION SELECT SUBSTRING(Person_id,1,1) as grp , Relative_id_2 FROM #temp_table WHERE Person_id is not null and Relative_id_1 is not null and Relative_id_2 is not null UNION SELECT SUBSTRING(Relative_id_1,1,1) as grp , Person_id FROM #temp_table WHERE Person_id is not null and Relative_id_1 is not null and Relative_id_2 is null UNION SELECT SUBSTRING(Relative_id_1,1,1) as grp , Relative_id_1 FROM #temp_table WHERE Person_id is not null and Relative_id_1 is not null and Relative_id_2 is null UNION SELECT SUBSTRING(Person_id,1,1) as grp , Person_id FROM #temp_table WHERE Person_id is not null and Relative_id_1 is null and Relative_id_2 is not null UNION SELECT SUBSTRING(Person_id,1,1) as grp , Relative_id_2 FROM #temp_table WHERE Person_id is not null and Relative_id_1 is null and Relative_id_2 is not null --SELECT * FROM #temp_data SELECT grp , STRING_AGG(family,',') WITHIN GROUP(ORDER BY family) as family INTO #temp_data_2 FROM #temp_data GROUP BY grp UNION SELECT SUBSTRING(a.Person_id,1,1) as grp, a.Person_id FROM #temp_table a LEFT JOIN #temp_data b ON a.Person_id = b.family WHERE b.family IS NULL SELECT 'F_' + CONVERT(NVARCHAR(10),ROW_NUMBER() OVER(ORDER BY grp)) as Family_id, family as Relatives FROM #temp_data_2 DROP TABLE #temp_table DROP TABLE #temp_data DROP TABLE #temp_data_2
@@techTFQ I was trying same in Oracle but was getting recursive loop error (ORA-32044). Tried cycle par set is_loop to 'Y' default 'N' (not sure if this helps) which didn't worked anyway.
SIR.. I HAVEA DOUBT 21:30 "FAM_GROUP" IS ONLY IN BASE QUERY RIGHT!! HOW CAN U TAKE THAT FROM RELATED FAM_MEMBERS? PLS EXPLAIN.. (IF IN TAMIL MEANS EVEN MOST SUPPORT)
Seems the similar query doesn't works with MSSQL. When I tried out the query is running in an endless loop though I had this joining condition... should we take only single record in base query and try the JOINS?
Hi Thoufiq. Thanks for your sharing. I notice that you are putting the base query "base_query" at the end and put the recursive query "related_fam_memebers" at the beginning. However, you are still able to use the base query in your recursive query. Does it mean that we can place our query in the with clause anywhere (at the beginning or end), we can still use the query in that with clause statement ? The ordering still does not matter.
Good question Tony and this depends on the RDBMS. In PostgreSQL, the order of temp tables do not matter but I know in SQL server or some other RDBMS does not support this.
Dear sir, do IT companies hire 35years old people with 2year gap as a fresher if he or she have strong technical skills( in coding) as a full stack developer .. please reply..
I think you're wrong cause the prblm is to combain person one to relative 1 and relative to to person 1 cos it relative to 2 .So let's just say there is another row with the following data person:ATR-7 relative1:ATR-8 and relative2 is null basically your query will put them along the atr family or F1 although they're not related to it so pls anybody explain me if am wrong
Sir if we have first name,last name and age and we have write a query where first name = Robert and lastname= Smith and sortby age how to write it sir please help
in sql server i ran the same query, recursive goes in infinite loop.Any idea ? with related_fam_members as (select * from ( select relative_id1 as relatives, substring(person_id,1,3) as family_group from family_members where relative_id1 is not null union select relative_id2 as relatives, substring(person_id,1,3) as family_group from family_members where relative_id2 is not null) base_query union all select fam.person_id, r.family_group from related_fam_members r join family_members fam on fam.relative_id1 = r.relatives or relative_id2 = r.relatives ) select * from related_fam_members option(maxrecursion 10000)
with recursive final_query as ( ( select relative_id1 as rel1, substring(person_id,1 , 3) as fam from family_members where relative_id1 is not null union select relative_id2 as rel1, substring(person_id,1 , 3) as fam from family_members where relative_id2 is not null order by 1) union (select m.person_id , f. fam from final_query f join family_members m on m.relative_id1 = f.rel1 or m.relative_id2 = f.rel1 ) ), nofamily as (select person_id from family_members where relative_id1 is Null and relative_id2 is null and person_id not in (select rel1 from final_query)) select *, concat("F_",row_number() over(order by family)) from ((select distinct group_concat(rel1 separator ',') as family from final_query group by fam order by 1) union all (select * from nofamily) order by 1) as x
you are getting ATR-1 ,BTR-1, DTR-2 ,DTR-3,CTR-3,ITR-1 by just joining , how is it possible? because both of the relative_id's are null right ?AND those id's present in the other table column values in the table. if what i am saying is right then what is the logic to get those respective person_id?
Because in with clause after base query you can see the union, so as taufqi said, base query will only excute at once, at first time the base query out has the out of relative _id1 union relative_id2. So, art1 will be there.
I wanted to know output for 1 table Create table patient (p_id int, p_name varchar(100),visits varchar(100)); Insert into patient select 1, 'raj', 'V1' from dual; Insert into patient select 1, 'raj', 'V3' from dual; Insert into patient select 1, 'raj', 'V4' from dual; Insert into patient select 2, 'divya', 'V2' from dual; Insert into patient select 2, 'divya', 'V4' from dual; Hw to write query for missed records for visits Eg: for p_id 1 visits column v2 is missing hw to fetch query
Hi Taufiq. while I'm solving this code in ms sql server it is giving an error like this: "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." do you know any the reason
You are the best TH-cam channel I have found for clear explanation on sql queries😄
Thank you Mukesh
Mash Allah the lecture really helpful for all IT techs
Glad to hear that
Please do a complete comprehensive course for sql on udemy platform so
We can get the maximum benefits .
Thanks sir
I do live sql training on Lighthall.. recorded courses, I may consider in the future
@@techTFQ please refer the course link
I start next batch from July end ..
Will announce all detail by this month end
Awesome again👍 Masha Allaah👌🏻
Thank you ❤️
Awesome. You explained it so nicely and made it simple for us to understand. Great job. Thanks a lot.
Glad you liked it Vamsi
Thanks for coming up everytime with new problem statements and help us learn something new. Pls keep posting!!
Thank you buddy, glad you liked it
I don’t know why they ask such questions in interview. It doesn’t solve any business problems. Anyways, you did a good job.
It’s more about to test your thinking ,
Even if you are not able to solve it, can you at least understand the logic required and how you go about solving such problems
@@techTFQ yes. I think white boarding is ideal. Btw, you are doing a great job. Keep it up!! Love your videos.
Sir, please make a video on SQL index and it's different types as well it would be really helpful.
Thank u so much Sir. Keep uploading
Your welcome buddy
really helpful sir thank you very much for your efforts and please keep upload like this type of videos.thank you so much
Thank you Surya, I will
Thank you Taufeeq 😊
Your welcome aaliya
Great! Really liked your lesson 👍
Glad to hear that!
Thank you for sharing man
Your welcome buddy
Such a great explainer 🙏...
Thank you Ashok
Excellent explanation and so helpful !!!!
This is really complex query
I learned something from here. Thanks kind sir. Awesome content as always. Keep uploading more videos like this and maybe next time something that solves a real life business problem as well.
Thank you James and sure will do!
Fantastic brother 👍🏻
Thank you buddy
please do a session on how to work with Json and regex in BQ. Thanks!
I’ll consider it Ara
I'm following for this point too. Please create a video for it
Hi brother how are you? we are learning so many good things about sql you are way of explanation is superb i have a small request can you please do using stored procedures we will learn that process also please
This is amazing!!!
Thank you very much 😊
Hi mentor, it was pleasure to subscribe your channel.. I m learning sql every nook and corner of the logic..im really grateful to come across ur channel... Thank u so much.. I have a doubt.. I'm 33 .im reforming my career in data analytics...could u share ur opinion
Hey TFQ, thanks for this great explanation
Let me also share you my solution for the same problem
I don't know if it is good practice or not but I tried to solve it differently and it worked
CREATE TABLE #temp_table (
Person_id NVARCHAR(1000)
, Relative_id_1 NVARCHAR(1000)
, Relative_id_2 NVARCHAR(1000)
)
INSERT INTO #temp_table
VALUES
('ATR-1',null,null),
('ATR-2','ATR-1',null),
('ATR-3','ATR-2',null),
('ATR-4','ATR-3',null),
('ATR-5','ATR-4',null),
('BTR-1',null,null),
('BTR-2',null,'BTR-1'),
('BTR-3',null,'BTR-2'),
('BTR-4',null,'BTR-3'),
('BTR-5',null,'BTR-4'),
('CTR-1',null,'CTR-3'),
('CTR-2','CTR-1',null),
('CTR-3',null,null),
('DTR-1','DTR-3','ETR-2'),
('DTR-2',null,null),
('DTR-3',null,null) ,
('ETR-1',null,'DTR-2'),
('ETR-2',null,null),
('FTR-1',null,null),
('FTR-2',null,null),
('FTR-3',null,null),
('GTR-1','GTR-1',null),
('GTR-2','GTR-1',null),
('GTR-3','GTR-1',null),
('HTR-1','GTR-1',null),
('HTR-2','GTR-1',null),
('HTR-3','GTR-1',null),
('ITR-1',null,null),
('ITR-2','ITR-3','ITR-1'),
('ITR-3',null,null);
--SELECT * FROM #temp_table
CREATE TABLE #temp_data ( grp NVARCHAR(10) , family NVARCHAR(1000) )
INSERT INTO #temp_data
SELECT SUBSTRING(Person_id,1,1) as grp , Person_id
FROM #temp_table
WHERE Person_id is not null and Relative_id_1 is not null and Relative_id_2 is not null
UNION
SELECT SUBSTRING(Person_id,1,1) as grp , Relative_id_1
FROM #temp_table
WHERE Person_id is not null and Relative_id_1 is not null and Relative_id_2 is not null
UNION
SELECT SUBSTRING(Person_id,1,1) as grp , Relative_id_2
FROM #temp_table
WHERE Person_id is not null and Relative_id_1 is not null and Relative_id_2 is not null
UNION
SELECT SUBSTRING(Relative_id_1,1,1) as grp , Person_id
FROM #temp_table
WHERE Person_id is not null and Relative_id_1 is not null and Relative_id_2 is null
UNION
SELECT SUBSTRING(Relative_id_1,1,1) as grp , Relative_id_1
FROM #temp_table
WHERE Person_id is not null and Relative_id_1 is not null and Relative_id_2 is null
UNION
SELECT SUBSTRING(Person_id,1,1) as grp , Person_id
FROM #temp_table
WHERE Person_id is not null and Relative_id_1 is null and Relative_id_2 is not null
UNION
SELECT SUBSTRING(Person_id,1,1) as grp , Relative_id_2
FROM #temp_table
WHERE Person_id is not null and Relative_id_1 is null and Relative_id_2 is not null
--SELECT * FROM #temp_data
SELECT grp , STRING_AGG(family,',') WITHIN GROUP(ORDER BY family) as family
INTO #temp_data_2
FROM #temp_data
GROUP BY grp
UNION
SELECT SUBSTRING(a.Person_id,1,1) as grp, a.Person_id
FROM #temp_table a
LEFT JOIN #temp_data b ON a.Person_id = b.family
WHERE b.family IS NULL
SELECT 'F_' + CONVERT(NVARCHAR(10),ROW_NUMBER() OVER(ORDER BY grp)) as Family_id, family as Relatives
FROM #temp_data_2
DROP TABLE #temp_table
DROP TABLE #temp_data
DROP TABLE #temp_data_2
Toufeeq i would appreciate if you start making videos on cosmos DB
Very Interesting
awesome thank you , also pls share how real time scenarios will be
You mean the sql we write in our jobs is it ?
@@techTFQ yes
noted, will plan something on this
I'm not fully positive about FTR-1, FTR-2, FTR-3 but I tihnk you could return them by using LEFT JOIN clause rather than JOIN in line 6
Very good videos ..
Can you please do video on using joins along with Aggregate functions ,where having ,group by order by
do check his channel, he has already posted that
As Arun said, the video is already made, plz check my channel
I am going to try to solve in Microsoft SQL Server
Nice.. good luck
Can you do any healthcare example please for people who works with hospital data
Hi Taufiq, thanks for the detailed explenation , i Guess you mentioned incorrect blog link as I am not able to see table queries.
Namaste, Sachin
Hello, please make video o how to download mysql or oracle in windows to practise queries in our local please.Thanks.
there are several videos on youtube which show this.
I do not have a windows computer else I could have made a video about it
hi brother can you please let me know when are you going to start new batch?
Sure bro, I’ll announce it 1 month prior to the start of class.. probably class starts from end of July
Thanks for the situation and solution. But I was wondering how it can be done in Oracle to avoid recursive loop, any ideas?
do you mean how to do this without using recursive sql in oracle?
@@techTFQ I was trying same in Oracle but was getting recursive loop error (ORA-32044). Tried cycle par set is_loop to 'Y' default 'N' (not sure if this helps) which didn't worked anyway.
You can just join the main table with the base query cte to get the group of people relatives, dont need to do recursive there.
Hi sir, when will you start live sessions
Hi Taufiq.. Thanks for wonderful explanation. I have been trying same code with SQL server but getting error on recursive table "related_fam_members;
sir plz provide video on views and indexes last video i never wont ask agin sir plzzzzzzzz
Hi, Could you please explain what’s the best way to write conditions in WHERE clause
I got confused here. is it that we always start from the base(anchor) query and then the recursive part? but this example puts the cursive first.
SIR.. I HAVEA DOUBT 21:30 "FAM_GROUP" IS ONLY IN BASE QUERY RIGHT!! HOW CAN U TAKE THAT FROM RELATED FAM_MEMBERS? PLS EXPLAIN.. (IF IN TAMIL MEANS EVEN MOST SUPPORT)
Hi bro can u plz upload videos on GCP
Seems the similar query doesn't works with MSSQL. When I tried out the query is running in an endless loop though I had this joining condition...
should we take only single record in base query and try the JOINS?
Hi one request can you add the ms sql query also for this. Becoming kind of confusing.
Sir can we use case statement to merge these 2columns into column3 and the. Work the solution
Hi Thoufiq. Thanks for your sharing. I notice that you are putting the base query "base_query" at the end and put the recursive query "related_fam_memebers" at the beginning. However, you are still able to use the base query in your recursive query. Does it mean that we can place our query in the with clause anywhere (at the beginning or end), we can still use the query in that with clause statement ? The ordering still does not matter.
Good question Tony and this depends on the RDBMS.
In PostgreSQL, the order of temp tables do not matter but I know in SQL server or some other RDBMS does not support this.
@@techTFQ what to do if that base_query does not support at top??
Mysql does not support this...im stuck now..
First view
Thanks buddy
Is there any alternative way to solve recursive sql queries?? And could you please provide some examples
Yes I'm using mode M and also I facing problem at the time of solving this query..please help me
Link to the blog is not working any more. Could you please help update ?
But this Query solve to inner join also
Am i right ?
sorry, dint get you bro.
are you asking if we can solve it using inner join alone?
@@techTFQ yes
bhai DVD ki full form digital video disk ha
sir can you give code for mysql rdbms also?
This week any video sir
hi bro... please clear me that...
what is data monkey and can a person jump from data monkey job to data science and data analytics.... please reply
Dear sir, do IT companies hire 35years old people with 2year gap as a fresher if he or she have strong technical skills( in coding) as a full stack developer .. please reply..
Sir can you pleaseexplain this query display the query having count more than 4 , cost price not between 4 and 5 thousand
Hi,
Recursive Query available in SQL Server?
Yes it is
I think you're wrong cause the prblm is to combain person one to relative 1 and relative to to person 1 cos it relative to 2 .So let's just say there is another row with the following data person:ATR-7 relative1:ATR-8 and relative2 is null basically your query will put them along the atr family or F1 although they're not related to it so pls anybody explain me if am wrong
Sir if we have first name,last name and age and we have write a query where first name = Robert and lastname= Smith and sortby age how to write it sir please help
in oracle DB
SELECT
first_name,
last_name,
age
FROM table_name
WHERE lower(first_name) = 'robert'
AND lower(last_name) = 'smith'
ORDER BY age;
I just asked one qns could you help me sir
This is not working in TSQL mssql server.
Link not working
in sql server i ran the same query, recursive goes in infinite loop.Any idea ?
with related_fam_members as
(select * from (
select relative_id1 as relatives, substring(person_id,1,3) as family_group
from family_members where relative_id1 is not null
union
select relative_id2 as relatives, substring(person_id,1,3) as family_group
from family_members where relative_id2 is not null) base_query
union all
select fam.person_id, r.family_group
from related_fam_members r
join family_members fam on fam.relative_id1 = r.relatives or relative_id2 = r.relatives
)
select * from related_fam_members
option(maxrecursion 10000)
Any answers ? Im curious to know about it
Hi Taufiq, how can we use CONNECT BY PRIOR clause for the said requirement in case of Oracle? Or perhaps any other technique in Oracle DB?
with recursive final_query as ( ( select relative_id1 as rel1, substring(person_id,1 , 3) as fam from family_members
where relative_id1 is not null union select relative_id2 as rel1, substring(person_id,1 , 3) as fam from family_members where relative_id2 is not null order by 1) union
(select m.person_id , f. fam from final_query f join family_members m on m.relative_id1 = f.rel1 or m.relative_id2 = f.rel1 ) ),
nofamily as (select person_id from family_members where relative_id1 is Null and relative_id2 is null and person_id not in (select rel1 from final_query))
select *, concat("F_",row_number() over(order by family)) from
((select distinct group_concat(rel1 separator ',') as family from final_query
group by fam order by 1)
union all
(select * from nofamily)
order by 1) as x
ATR2 is related to ATR1 but ATR1 is not related to ATR2 :D
where is code
you are getting ATR-1 ,BTR-1, DTR-2 ,DTR-3,CTR-3,ITR-1 by just joining , how is it possible? because both of the relative_id's are null right ?AND those id's present in the other table column values in the table.
if what i am saying is right then what is the logic to get those respective person_id?
Because in with clause after base query you can see the union, so as taufqi said, base query will only excute at once, at first time the base query out has the out of relative _id1 union relative_id2. So, art1 will be there.
Hi
I wanted to know output for 1 table
Create table patient (p_id int, p_name varchar(100),visits varchar(100));
Insert into patient select 1, 'raj', 'V1' from dual;
Insert into patient select 1, 'raj', 'V3' from dual;
Insert into patient select 1, 'raj', 'V4' from dual;
Insert into patient select 2, 'divya', 'V2' from dual;
Insert into patient select 2, 'divya', 'V4' from dual;
Hw to write query for missed records for visits
Eg: for p_id 1 visits column v2 is missing hw to fetch query
Hi Taufiq. while I'm solving this code in ms sql server it is giving an error like this:
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
do you know any the reason
what is the terminating condition do we have to use
@@22_vamshikrishnadathrika31 did you get the solution? I'm facing the same issue in sql server
No
What is your sql fees
Pls tell me when new batch will be started??
DROP TABLE IF EXISTS public.relatives;
CREATE TABLE IF NOT EXISTS public.relatives
(
PERSON_ID text,
RELATIVE_ID1 text,
RELATIVE_ID2 text
);
INSERT INTO relatives
VALUES (
('ATR-1',NULL,NULL),
('ATR-2','ATR-1',NULL),
('ATR-3','ATR-2',NULL),
('ATR-4','ATR-3',NULL),
('ATR-5','ATR-4',NULL),
('BTR-1',NULL,NULL),
('BTR-2',NULL,'BTR-1'),
('BTR-3',NULL,'BTR-2'),
('BTR-4',NULL,'BTR-3'),
('BTR-5',NULL,'BTR-4'),
('CTR-1',NULL,'CTR-3'),
('CTR-2','CTR-1',NULL),
('CTR-3',NULL,NULL),
('DTR-1','DTR-3','ETR-2'),
('DTR-2',NULL,NULL),
('DTR-3',NULL,NULL),
('ETR-1',NULL,'DTR-2'),
('ETR-2',NULL,NULL),
('FTR-1',NULL,NULL),
('FTR-2',NULL,NULL),
('FTR-3',NULL,NULL);