| View previous topic :: View next topic |
| Author |
Message |
veluvpv
Beginner
Joined: 14 Jul 2008
Posts: 9
Topics: 1
|
 Posted: Tue Jan 25, 2011 6:14 am Post subject: Outer joins |
 |
|
Hi All,
I surfed a lot on my below requirement, but couldn't get the exact solutions anywhere. Please guide me thru this,
My Req:
I have 3 tables, say tablea,tableb and tablec. Table a and table b have have common key field, table b and table c have common key field. but there is no relation between table a and table c.
i want to count and extract the details based on the age range present in table a.
| Code: |
select count(*),a.lowerlmt,a.upperlmt,b.security,c.number1
from (
tablea
left outer join
tableb
on
a.type = b.type and
a.age between b.lowerlmt and b.upperlmt
)
left outer join
table c on
b.key1 = c.key
where b.type = 'xxx'
group by a.lowerlmt,a.upperlmt,b.security,c.number1
order by 5,4
|
table values tablea
| Code: |
lowerlmt upperlmt type
1 5 xxx
6 10 xxx
11 15 xxx
16 20 xxx
|
and my o/p is
| Code: |
count lowerlmt upperlmt security number1
2 1 5 123 10001
1 10 15 123 10001
3 6 10 1234 10001
0 16 20 ----- -------
|
expected o/p
| Code: |
count lowerlmt upperlmt security number1
2 1 5 123 10001
0 6 10 ---- -------
1 11 15 123 10001
0 16 20 ------ ------
0 1 5 ----- -------
3 6 10 1234 10001
0 11 15 ------ ------
0 16 20 ------ ------
|
i.e For every age range in tablea, we should get a value in the o/p grouped by number and security.
hope this is clear.
Thanks in advance,
VPV |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose
|
| Posted: Tue Jan 25, 2011 11:37 am Post subject: |
|
|
veluvpv,
Your table data does not match the Output values. How did you get a count value of 2 for the range 1 - 5?
Where is the security and number coming from?
Show us complete tables
Kolusu |
|
| Back to top |
|
|
veluvpv
Beginner
Joined: 14 Jul 2008
Posts: 9
Topics: 1
|
| Posted: Tue Jan 25, 2011 1:42 pm Post subject: |
|
|
Kolusu,
Apologize for the missed info. please find the details below,
tableb has keyfield1 (which is common to tablea),number (FK - Tablec. this is the field common to table b and tablec), age,security.
tableb
| Code: |
type(keyfield1) num1 age security
XXX 101 3 123
XXX 101 3 123
XXX 101 7 123
XXX 102 12 1234
|
tablec
| Code: |
num1 number1
101 10001
102 10002
|
tablea
| Code: |
type lowerlmt upperlmt
XXX 1 5
XXX 6 10
XXX 11 15
|
my expected output is to count the total no. of rows from tableb based on the age range (lower limit and upper limit from table a) and should be grouped by number1(in tablec) and security.
expected o/p
| Code: |
count number1 security lowerlmt upperlmt
2 100001 123 1 5
1 100001 123 6 10
0 or ' ' 100001 123 11 15
0 100002 1234 1 5
0 100002 1234 6 10
1 100002 1234 11 15
|
i.e for each age range from the tablea there should be some entry.. which means if we have values in that age range that count will be accumulated else it need to display spaces or any other values.
Thanks,
Veluvpv |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose
|
| Posted: Tue Jan 25, 2011 2:57 pm Post subject: |
|
|
veluvpv,
Untested sql but hopefully it should give you the desired results.
| Code: |
SELECT SUM(CASE WHEN D.AGE BETWEEN A.LOWERLIMIT AND A.UPPERLIMIT
THEN INT(1) ELSE INT(0) END) AS COUNT
,D.NUMBER1
,D.SECURITY
,A.LOWERLIMIT
,A.UPPERLIMIT
FROM your_tableA A
,(SELECT B.SECURITY
,B.AGE
,C.NUMBER1
FROM your_tableB B
,your_tableC C
WHERE B.NUM1 = C.NUM1) D
GROUP BY D.NUMBER1
,D.SECURITY
,A.LOWERLIMIT
,A.UPPERLIMIT
; |
Kolusu |
|
| Back to top |
|
|
veluvpv
Beginner
Joined: 14 Jul 2008
Posts: 9
Topics: 1
|
| Posted: Thu Jan 27, 2011 6:48 am Post subject: |
|
|
Kolusu..!!!
you are REALLY GREAT..!!! 8)  
it worked out and i got the expected result.
thanks a bunch..!!
Regards,
VPV |
|
| Back to top |
|
|
veluvpv
Beginner
Joined: 14 Jul 2008
Posts: 9
Topics: 1
|
| Posted: Tue Feb 01, 2011 7:57 am Post subject: |
|
|
Kolusu,
As said in the previous post, the query worked fine.. Now the question is about performance   . I have huge data in the db  . So is it possible to fine tune the SQL? 
Appreciate your help on this regard too.
Regards,
VPV |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
veluvpv
Beginner
Joined: 14 Jul 2008
Posts: 9
Topics: 1
|
| Posted: Wed Feb 02, 2011 4:23 am Post subject: |
|
|
Kolusu,
Apologize for the mistake. Will search before posting hence forth.
Thanks for the reply.
Regards,
VPV |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
