Solving an SQL Interview Problem | Find Child - Parent - Grandparent Hierarchy using SQL

[@techTFQ]

OdinSchool Bootcamp link: hubs.la/Q024h4NC0 Success stories page link: hubs.la/Q024h4Xz0

[@naveenkumar-pm2si]

Hi, Thought of registering for your sql course but couldnt find a link Could you please help me on this

[@parvchopra1456]

hii sir hope you are doing well sir could you please make a video or could you please tell ,how to master SQL ?

[@sujaa1000]

One of the best KZbin channel for SQL! Thanks TFQ!

[@zewduwereta302]

Very interesting problem! I like the way you dissect the problem into different parts, it makes easier to approach it! Thanks!👍👍

[@Madhusudan_Sarkate]

Best explanation ever hard problems look like simple

[@560FarazAhmad]

JazakAllah. Very convenient solution to a complex problem. May Allah bless you.

[@techTFQ]

Thank you very much 😊

[@attilapekkaszabo-iv6tl]

Best tutor for SQL, easy to follow and very detailed explanation. Must follow and subscribe if you want to solve interesting SQL problems.

[@Samcrazy4u]

Thanks toufiq Bhai, for helping Data community and and sharing knowledge & experience 😊, it helps a lot to understand self join

[@SrikanthSSSSSS]

Thank you sir, I developed my SQL with your videos only

[@JordanTambeck]

great video as always, it was well explained.

[@avi8016]

Great explanation of self join Thankyou sir for this video!

[@reachbharathan]

I think this problem should be the best example for demoing the usecase for self join

[@krantikumar894]

Self join is the best way to answer this question

[@ashu7863]

Please share this session also

[@Manzur.A]

You are the BEST!

[@AviralOjha]

May Allah always be by your side my brother. You are like one of his greatest blessings on 🌎❤ The way you have explained and brought the best solution out is commendable! Ankit Bansal makes things over complicated, neither do I enjoy his style of teaching. We are meant to learn from you!

[@techTFQ]

Thank you very much brother 😊 All the best

[@soundarsswag7386]

Select count(1) from person a Where status =Alive And exists ( select 1 from persons Where a.parent = b. Person)

[@venkeenkokku]

This correlated sub-query will yield the result as required --- select c.person from persons c where exists ( select null from persons p where c.parent = p.person and exists ( select null from persons g where g.person= p.parent and g.status = 'Alive'))

[@fathimafarahna2633]

Great video again 👍👍

[@gontlakowshik2345]

We can use self join

[@Ujjwalmishra-t1w]

Nice questions

[@Krishna48784]

Thank you for sharing, nice explanation. When will you start postgresql Boot camp

[@reachbharathan]

Thanks a lot , I understood everything, great explanation, i question what the last part select count( 1) , are we doing a grouping?

[@vinodlondhe-cm8lz]

Your great sir

[@sachingunjal7347]

hope this solution is acceptable -- select P1.person as self, P2.person as Parent, P3.Person as GP, P1.person_status as GP_Status from Persons as P1 Right join Persons as P2 on P1.parent = P2.Person right join Persons as P3 on P2.parent = P3.Person where P1.person_status = 'Alive'

[@santhoshkumar6760]

Solved the same kind of problem with scenario employee, manager, managers_ manager using self join

[@santhoshkumar6760]

N i loved the way of explanation, it's giving interest more n more Thanks

[@Orkhan-w5r]

Thank you, very useful video. I have a similar task but with more than 10 levels of hiereachy. Not sure if this is a good idea to use self join more than 10 times. Any suggestions?

[@matthias7534]

Cde with recursive select query

[@matthias7534]

He made a video on it as well kzbin.info/www/bejne/gouqkoWZed2dbqs

[@aravindt1596]

Hi I want to triggers in postgresql

[@aimanansarmomin4542]

I am not from technical background but learning sql. When we open list of the tables ,I am not able to find the parent table among all tables please guide me on this..

[@ParhamFarjam-n3b]

Hello, Would you please solve quastion like this for python(panda)?

[@swethaveluguri9115]

I'm facing a SQL interview question repeateadly. "Why a stored preocedure is executing fine till yesterday and today it is taking long time to execute" [indexes are not dropped/no changes to this query]. Can you please explain this question ?

[@venkeenkokku]

Either data in the table got added over period of time, which caused underlying data distribution change OR Failure to refresh to stats on regular basis OR both

[@CheatingStoriesWithChinwe]

Tfq makes sql look so simple

[@azalieysakova]

thanks a lot!!!!!!!

[@sameerambre2696]

I am not sure whether I am right here but, this is what I understand. Parent of B is Y and Y's grandparent is Y1 and the status of Y1 is Alive. Can you please help me understand why that record is not coming in your result set?

[@PriyankaVijay-pm1wc]

I hope you are checking the status of y1 in parent table. You should see y1 status from name of person then check the status of the person . X1 is alive and y1 is dead

[@throughmyglasses9241]

with recursive parents as ( select person,parent,CAST(NULL as varchar(10)) as son, 1 as level from people where person not in (select parent from people) union all select p2.person,p2.parent,p1.person,level+1 from parents as p1 inner join people as p2 on p1.parent=p2.person where p2.status='Alive') select count(*) from parents where level>=3;

[@nivisworld1513]

sir.....please post sql classes from scratch......you said will post 1st week of September but not posted......please sir post those classes.......

[@swapnilsolanki8595]

Another way of writing query for same problem with recursive cte1 as ( select * from test where par_name in (select p_name from test) ), cte2 as ( select *, 1 as level from cte1 union select c1.*, level + 1 as level from cte1 c1 join cte2 c2 on c2.par_name = c1.p_name ) select count(1) from ( select cte2.p_name, cte2.par_name, t.status from cte2 join test t on cte2.par_name = t.p_name where level = 2 and t.status = 'Alive' ) subquery

[@rishabhgupta1029]

with table_A as ( select t1.*, t2.parent as grand_parents from persons as t1 left join persons as t2 on t1.parent = t2.person ) select count(*) from table_A as t1 left join persons as t2 on t1.grand_parents = t2.person where t2.person_status ='Alive' ;

[@krishnakumarkumar5710]

Query solving with Odni school ad

[@Rajendra-b6t]

SELECT count(1) FROM persons c JOIN persons P ON p.PERSON = c.parent join persons GP on GP.PERSON = p.parent where GP.person_status = 'Alive'

[@palbabu33]

posting before watching your solution: SELECT C.PERSON AS CHILD, C.PARENT AS PARENT, P.PARENT AS GPARENT, GP.PERSON_STATUS FROM STATUS C JOIN STATUS P ON C.PARENT = P.PERSON JOIN STATUS GP ON P.PARENT = GP.PERSON WHERE GP.PERSON_STATUS = 'ALIVE';

[@sreejeshjnair6755]

Self join is a solution

[@varuntirupati2566]

select count(p.person) as persons from persons p inner join persons gp on p.parent = gp.person inner join persons gp2 on gp.parent = gp2.person where gp2.person_status = 'Alive';

[@anudeepreddy5559]

Plsql bootcamp please

[@satyamace]

I don't know why my one line code is working perfectly: select person from persons where parent in (select person from persons where parent in (select person from persons where person_status='Alive'));

[@vrajsomeshvari3825]

great

[@techTFQ]

SQL Server problem solving

[@HARSHRAJ-wz2rp]

select * from persons; with cte as( select P2.person,P1.parent as "Grandfather" FROM persons P1 JOIN persons P2 ON P1.person=p2.parent ) select COUNT(*) FROM cte JOIN persons ON cte.Grandfather=persons.person where person_status="Alive"; hoto gaya per kaise gaya woh nahi pata

[@KoushikT]

################ My Solution using CTE ################ with A as ( select p1.person as person, p2.parent as grandparent from persons p1 join persons p2 on p1.parent=p2.person) select count(A.person) as alive_count from A join persons p on A.grandparent = p.person where p.person_status='Alive'

[@vishalsonawane.8905]

Done

[@DeanMarko-ex3rd]

Not be annoying but this is a dumb question. But I love your videos

[@cartercordingley6062]

I use a question like this but with an org chart. And I throw in n levels. Of find all employees under the CSO This question let me know if they can understand how to loop through an unknown amount of data. A lot of people will not use loops and just use joins, which is incorrect. But if they use recursive cte, cursor, or even pass it to a CLR or other program like Python or a C# to do a loop, they pass.

[@TheAbhisai]

Natural joins

[@shashigowdar7359]

self join

[@ChanduMuddasani]

Please explain slow and clear I didn't understand this vedio

[@viswaskumarsurapaneni72]

Slefjoin a 1 with a2 on a1.person =a2.parent

[@abir95571]

You’ll have to do 3 self joins as the live status is on person not parent.

[@sss-hk9tt]

Pks explain snowflakes in sql

[@SumathyG-c1z]

Self join

[@Oracle_developer]

Self join

[@shitalredij]

self join