10G SQL query

[y@hoo]

Im trying to run a query like this:
Display the names of pairs of customers who have booked the same room together with the details of the location and room.

I did try like this:

select cust_name, room_name, cost_per_day, capacity, loc_name, avenue_site.address
from customer, aroom, booking, avenue_site
WHERE aroom.room# = booking.room#
and customer.customer# = booking.customer#
and avenue_site.location# = aroom.location#
;

but all I got was:

CUST_NAME                 ROOM_NAME       COST_PER_DAY           CAPACITY               LOC_NAME        
------------------------- --------------- ---------------------- ---------------------- --------------- 
Conferences4U             McGordon Hall   1100                   125                    Union Centre    
Art 2 the People          McGordon Hall   1100                   125                    Union Centre    
Art 2 the People          Robert Hall     300                    30                     Union Centre    
Art 2 the People          Robert Hall     300                    30                     Union Centre    
Conferences4U             Lavender Room   2000                   200                    Garthdee Centre 
Art 2 the People          Swallow Hall    3700                   290                    Dyce Centre

6 rows selected

[debasisdas]

kindly post your table structure.

[y@hoo]

sry ;)

ROOM# LOCATION# ROOM_NAME       COST_PER_DAY           CAPACITY               
----- --------- --------------- ---------------------- ---------------------- 
R001  L0001     Robert Hall     300                    30                     
R002  L0001     McGordon Hall   1100                   125                    
R003  L0002     Green Room      950                    75                     
R004  L0002     Lavender Room   2000                   200                    
R005  L0002     Ruby Room       2725                   300                    
R006  L0003     Swallow Hall    3700                   290                    

6 rows selected

LOCATION# LOC_NAME        ADDRESS              TEL_NO       
--------- --------------- -------------------- ------------ 
L0001     Union Centre    703 Union Place      01234 567890 
L0002     Garthdee Centre 623 Garthdee Lane    09876 543210 
L0003     Dyce Centre     21 Dyce Terrace      01928 374655 

3 rows selected

CUSTOMER# CUST_NAME                 ADDRESS                        TEL_NO       DISCOUNT               
--------- ------------------------- ------------------------------ ------------ ---------------------- 
C0005     Conferences4U             302 St. Andrew St              01111 111111 10                     
C0006     Wedding Bells             222 Bridge Road                09999 999999 0                      
C0007     Art 2 the People          22 Market Terrace              05555 555555 5                      

3 rows selected

BOOKING# CUSTOMER# ROOM# FROM_DATE                 TO_DATE                   N_PEOPLE               
-------- --------- ----- ------------------------- ------------------------- ---------------------- 
B000001  C0007     R001  03-MAR-09                 03-MAR-09                 28                     
B000002  C0007     R001  07-MAR-09                 07-MAR-09                 28                     
B000003  C0005     R004  03-MAR-09                 08-MAR-09                 125                    
B000004  C0007     R006  02-MAR-09                 04-MAR-09                 267                    
B000005  C0005     R002  07-MAR-09                 07-MAR-09                 96                     
B000006  C0007     R002  09-MAR-09                 09-MAR-09                 104                    

6 rows selected

[y@hoo]

still havent sort it out, got a hint to do it using fragmentation...
maybe that will help??

[mir77]

Use group by clause and use group_column>1 in the where condition