| Author | Message | ||
     Driverramudu Side Hero Username: Driverramudu Post Number: 5862 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
yep I did sent a msg Life is Race. I am in. Driving is my PASSION. | ||
| Pulpfiction Hero Username: Pulpfiction Post Number: 19234 Registered: 02-2009 Posted From: 183.82.97.43 Rating: N/A |
bro can u come online on yahoo messenger pulpfictionccdb | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5861 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
r u sure?  Life is Race. I am in. Driving is my PASSION. | ||
| Pulpfiction Hero Username: Pulpfiction Post Number: 19233 Registered: 02-2009 Posted From: 183.82.97.43 Rating: N/A |
the queries arent working ..  | ||
| Dreamcatcher Side Hero Username: Dreamcatcher Post Number: 2684 Registered: 11-2009 Posted From: 208.86.145.68 Rating: N/A |
Exactly, nenu kooda idey anukunna. | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5850 Registered: 02-2009 Posted From: 198.228.222.126 Rating: N/A |
Yep you can have simple one table with The name Family Having columns like personId, name ParentPersonId that's it Mottham khandhaan info set cheyavacchu  Life is Race. I am in. Driving is my PASSION. | ||
| Dreamcatcher Side Hero Username: Dreamcatcher Post Number: 2682 Registered: 11-2009 Posted From: 208.86.145.68 Rating: N/A |
Why do you need a grandchild table bro? isn't grandchild child of a father? You already have tables to capture father to child relationship. | ||
| Sachin Megastar Username: Sachin Post Number: 27856 Registered: 04-2008 Posted From: 209.249.19.126 Rating: N/A |
vaaake share ki gross ki thedaa theliyakapothenemi..daatar maaya box office disco lo iragadeesthaaadu... ki ki ki ... | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5848 Registered: 02-2009 Posted From: 198.228.222.126 Rating: N/A |
Would explain later with xamples  Life is Race. I am in. Driving is my PASSION. | ||
| Sachin Megastar Username: Sachin Post Number: 27853 Registered: 04-2008 Posted From: 209.249.19.126 Rating: N/A |
ardham kaaledu bhayyaaaa share ki gross ki thedaa theliyakapothenemi..daatar maaya box office disco lo iragadeesthaaadu... ki ki ki ... | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5847 Registered: 02-2009 Posted From: 198.228.222.126 Rating: N/A |
Ivanni chaala chinna chinna simple queries Nenu mastu complex queries Almost total sequential program converted As set queries and achieved the same I never ever used single cursor sine many Years College days lone realize ai unte gee batiki GC setlio undedi Life is Race. I am in. Driving is my PASSION. | ||
| Shikari Side Hero Username: Shikari Post Number: 7626 Registered: 03-2010 Posted From: 183.82.187.70 Rating: N/A |
class leader aithe http://x.co/bgEg http://x.co/bgEb http://www.youtube.com/watch?v=VXfXghRxy9M http://x.co/bgEk | ||
| Sachin Megastar Username: Sachin Post Number: 27852 Registered: 04-2008 Posted From: 209.249.19.126 Rating: N/A |
driver vuncle 1st bench student share ki gross ki thedaa theliyakapothenemi..daatar maaya box office disco lo iragadeesthaaadu... ki ki ki ... | ||
| Shikari Side Hero Username: Shikari Post Number: 7624 Registered: 03-2010 Posted From: 183.82.187.70 Rating: N/A |
tution lo allari cheyakunda chaduvukuntunnaru enti?  http://x.co/bgEg http://x.co/bgEb http://www.youtube.com/watch?v=VXfXghRxy9M http://x.co/bgEk | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5844 Registered: 02-2009 Posted From: 198.228.222.126 Rating: N/A |
If u want just count and if u r using SQL server then use below one query Select f.fatherid, c. ChildCount,gc.grandchildcount From father as f Outer apply ( Select count(1) as ChildCount From child as ic Where ic.fatherid = f.fatherid ) as c Outer apply ( Select count(1) as grandchildcount From grandchild as igc Where igc.fatherid = f.fatherid ) This will give just count under father context Life is Race. I am in. Driving is my PASSION. | ||
| Just4fun Megastar Username: Just4fun Post Number: 25452 Registered: 10-2007 Posted From: 174.37.61.78 Rating: N/A |
 | ||
| Sachin Megastar Username: Sachin Post Number: 27851 Registered: 04-2008 Posted From: 209.249.19.126 Rating: N/A |
-- number of children per father who have at least one grandchild WITH father_with_grandchild AS ( SELECT fatherid FROM grandchild GROUP BY fatherid HAVING COUNT(*) >1) SELECT fatherid, count(*) no_of_childern FROM father JOIN child ON ( father.fatherid = child.fatherid) JOIN father_with_grandchild fwg ON ( father.fatherid = fwg.fatherid) GROUP BY fatherid -- number of children per father who have less than 3 granchild WITH sub_set AS ( SELECT fatherid FROM grandchild GROUP BY fatherid HAVING COUNT(*) < 3 ) SELECT fatherid, count(*) no_of_childern FROM father JOIN child ON ( father.fatherid = child.fatherid) JOIN sub_set fwg ON ( father.fatherid = fwg.fatherid) GROUP BY fatherid -- number of children per father who have more than 3 grandchildren without passport number WITH sub_set AS ( SELECT fatherid FROM grandchild WHERE PassportNumber IS NULL GROUP BY fatherid HAVING COUNT(*) > 3 ) SELECT fatherid, count(*) no_of_childern FROM father JOIN child ON ( father.fatherid = child.fatherid) JOIN sub_set fwg ON ( father.fatherid = fwg.fatherid) GROUP BY fatherid share ki gross ki thedaa theliyakapothenemi..daatar maaya box office disco lo iragadeesthaaadu... ki ki ki ... | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5843 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
exists and not exists is better than in or not in expecailly when you know NULL may be there in sub-query for performance another thing is use top clause instead of count or selecting some columns in the sub query this is one of the performance query tuning concept Life is Race. I am in. Driving is my PASSION. | ||
| Just4fun Megastar Username: Just4fun Post Number: 25451 Registered: 10-2007 Posted From: 66.207.139.14 Rating: N/A |
KEKA.. I never used 'exists' in oracle..so enta comple query aina pai format lo rasestunta | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5842 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
then it works again if father table added this it complex the query so i used and peferred exists Life is Race. I am in. Driving is my PASSION. | ||
| Just4fun Megastar Username: Just4fun Post Number: 25449 Registered: 10-2007 Posted From: 66.207.139.14 Rating: N/A |
just add group by kaka select c.child_id,count(c.child_id) from Child c,Grand_Child gc where c.child_id=gc.child_id group by c.child_id | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5838 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
yep bro I confirmed Life is Race. I am in. Driving is my PASSION. | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5837 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
ikkada per child or father annappudu under that context you have to show count where as in above query give total count not for each child entamandi grand child ani ivvadu Life is Race. I am in. Driving is my PASSION. | ||
| Pulpfiction Hero Username: Pulpfiction Post Number: 19227 Registered: 02-2009 Posted From: 183.82.97.43 Rating: N/A |
driver bro can u confirm my last post query is the final one | ||
| Pulpfiction Hero Username: Pulpfiction Post Number: 19226 Registered: 02-2009 Posted From: 183.82.97.43 Rating: N/A |
select f.fatherId,count(c.childId) from child as c inner join father as f on f.fatherid = c.fatherid where exists ( select top 1 1 from grandchild as gc where gc.childid = c.childid ) group by f.fatherid select f.fatherId,count(c.childId) from child as c inner join father as f on f.fatherid = c.fatherid where 3 > ( select count(1) from grandchild as gc where gc.childid = c.childid ) group by f.fatherid select f.fatherId,count(c.childId) from child as c inner join father as f on f.fatherid = c.fatherid where 3 < ( select count(1) from grandchild as gc where gc.childid = c.childid and gc.passportnumber is null ) group by f.fatherid | ||
| Just4fun Megastar Username: Just4fun Post Number: 25448 Registered: 10-2007 Posted From: 174.37.61.78 Rating: N/A |
@Driver kaka 1st ans ila rayachha? select count(*) from Child c,Grand_Child gc where c.child_id=gc.child_id | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5836 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
u welcome bro Life is Race. I am in. Driving is my PASSION. | ||
| Pulpfiction Hero Username: Pulpfiction Post Number: 19225 Registered: 02-2009 Posted From: 183.82.97.43 Rating: N/A |
thanks brother | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5835 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
copy and paste kada Life is Race. I am in. Driving is my PASSION. | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5834 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
sorry add group by f.fatherid for all three queries Life is Race. I am in. Driving is my PASSION. | ||
| Outlaw Junior Artist Username: Outlaw Post Number: 221 Registered: 02-2012 Posted From: 66.193.93.3 Rating: N/A |
Appude raasi dobbava keka. | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5833 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
select f.fatherId,count(c.childId) from child as c inner join father as f on f.fatherid = c.fatherid where exists ( select top 1 1 from grandchild as gc where gc.childid = c.childid ) select f.fatherId,count(c.childId) from child as c inner join father as f on f.fatherid = c.fatherid where 3 > ( select count(1) from grandchild as gc where gc.childid = c.childid ) select f.fatherId,count(c.childId) from child as c inner join father as f on f.fatherid = c.fatherid where 3 < ( select count(1) from grandchild as gc where gc.childid = c.childid and gc.passportnumber is null ) Life is Race. I am in. Driving is my PASSION. | ||
| Pulpfiction Hero Username: Pulpfiction Post Number: 19224 Registered: 02-2009 Posted From: 183.82.97.43 Rating: N/A |
driver annai .. thanks .. please see my latest requirements | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5832 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
select * from child as c where exists ( select top 1 1 from grandchild as gc where gc.childid = c.childid ) select * from child as c where 3 > ( select count(1) from grandchild as gc where gc.childid = c.childid ) select * from child as c where 3 < ( select count(1) from grandchild as gc where gc.childid = c.childid and gc.passportnumber is null ) Life is Race. I am in. Driving is my PASSION. | ||
| Pulpfiction Hero Username: Pulpfiction Post Number: 19223 Registered: 02-2009 Posted From: 183.82.97.43 Rating: N/A |
sorry this is what is needed number of children per father who have at least one grandchild number of children per father who have less than 3 granchild number of children per father who have more than 3 grandchildren without passport number | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 5831 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
then wat is urs Q Life is Race. I am in. Driving is my PASSION. | ||
| Outlaw Junior Artist Username: Outlaw Post Number: 220 Registered: 02-2012 Posted From: 66.193.93.3 Rating: N/A |
then what? | ||
| Pulpfiction Hero Username: Pulpfiction Post Number: 19222 Registered: 02-2009 Posted From: 183.82.97.43 Rating: N/A |
need queries for number of children who have at least one grandchild number of children who have less than 3 granchild number of children who have more than 3 grandchildren without passport number | ||
| Pulpfiction Hero Username: Pulpfiction Post Number: 19221 Registered: 02-2009 Posted From: 183.82.97.43 Rating: N/A |
no kindaling please | ||
| Pulpfiction Hero Username: Pulpfiction Post Number: 19220 Registered: 02-2009 Posted From: 183.82.97.43 Rating: N/A |
Table Called Father with only one field 'FatherId' Table Called Child with two fields 'ChildId' and 'FatherId' Table Called Grand Child with four fields 'GrandChildId' , 'Child Id', 'FatherId' , 'PassportNumber' |
