URGENT!!COUNT()??? [message #22840] |
Thu, 31 October 2002 22:48 |
TECH
Messages: 3 Registered: October 2002
|
Junior Member |
|
|
HELLO EVERYONE
I HAVE A TABLE - PROPERTY:
SUBURB
----------
bondi
bronte
clovelly
coogee
hillsdale
kingsford
maroubra
randwick
waverley
I ALSO HAVE ANOTHER TABLE - P1
SUBURB NUMBER#
-------------------- ----------
clovelly 2
coogee 3
randwick 1
NOW I WANT TO DISPLAY SUCH THAT FOR EACH
SUBURB IN PROPERTY, IT WILL SHOW THE NUMBER
IN P1 IF THE SUBURB CORRESPONDS OTHERWISE
DISPLAY ZERO.
SUBURB NUMBER#
-------------------- ----------
bondi 0
bronte 0
clovelly 2
coogee 3
hillsdale 0
kingsford 0
maroubra 0
randwick 1
waverley 0
PLEASE HELP OUT!
CHEERS
TECH
|
|
|
Re: URGENT!! [message #22841 is a reply to message #22840] |
Thu, 31 October 2002 23:13 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Outer join:
select p.suburb, nvl(p1.num, 0)
from property p, p1
where p1.suburb (+) = p.suburb
order by p.suburb;
|
|
|
Re: URGENT!!COUNT()??? [message #22845 is a reply to message #22840] |
Fri, 01 November 2002 07:56 |
sridhar
Messages: 119 Registered: December 2001
|
Senior Member |
|
|
Alternatively,
Select suburb, number From p1
Union
Select p2.suburb, 0 From property p2
Where not exists(Select 'x' From p1 Where p1.suburb = p2.suburb);
Thx,
SriDHAR
|
|
|