Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join Question
Outer Join Question [message #196121] Wed, 04 October 2006 03:41 Go to next message
AbuShreek
Messages: 22
Registered: May 2006
Junior Member
Hello,

I have the following structure:
DATE_TIME
----------------
DATE

STREETS
----------------
STREET
CITY
REGION

TRAFFIC
----------------
DATE
STREET
CITY
CARS_IN_HOUR


I need to display the traffic for all streets in a city in a certain date. I need to display a ZERO in CARS_IN_HOUR even if the street didn’t have a record for that particular date in TRAFFIC table as long as it appears in STREETS table. Basically what I need is an outer join. The problem that I have is that TRAFFIC table is joined to two tables. Whenever I try to OUTER join both tables it gives “a table maybe outer joined to at most one other table” error.

What I am writing is:
SELECT TRAFFIC.* 
FROM DATE_TIME, STREETS, TRAFFIC
WHERE TRAFFIC.DATE = DATE_TIME.DATE
AND TRAFFIC.STREET = STREETS.STREET AND TRAFFIC.CITY = STREETS.CITY
AND TO_CHAR (DATE, 'DD-MM-YYYY') = '03-10-2006'
AND STREETS.REGION = ‘SOUTH’


Where can I put the outer join marks? When I put the (+) beside TRAFFIC.DATE, TRAFFIC.STREET and TRAFFIC.CITY it gives the above error. When I put it only beside TRAFFIC.DATE or TRAFFIC.STREET and TRAFFIC.CITY it returns only the streets that exists in TRAFFIC table and not all of those listed in STREETS table.

Please help.

Thanks in advance.
Re: Outer Join Question [message #196124 is a reply to message #196121] Wed, 04 October 2006 03:57 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
SELECT 
     STREETS.STREET, 
     STREETS.CITY, 
     STREETS.REGION, 
     DATETIME.DATE,
     TRAFFIC.CARS_IN_HOUR
FROM DATE_TIME, 
     STREETS, 
     (SELECT * FROM TRAFFIC 
      WHERE TO_CHAR (TRAFFIC.DATETIME, 'DD-MM-YYYY') = 
        '03-10-2006') TRAFFIC
WHERE TRAFFIC.DATE = DATE_TIME.DATE (+)
AND TRAFFIC.STREET (+) = STREETS.STREET 
AND TRAFFIC.CITY (+) = STREETS.CITY
AND STREETS.REGION = 'SE'

[Updated on: Wed, 04 October 2006 04:54]

Report message to a moderator

Re: Outer Join Question [message #196171 is a reply to message #196124] Wed, 04 October 2006 07:06 Go to previous messageGo to next message
AbuShreek
Messages: 22
Registered: May 2006
Junior Member
Thanks alot, BUT....

I want to preserve the FROM clause as is becuase this SQL is basically generated from Business Objects and I dont have so much control on how it is generated.

I want to modify the WHERE caluse to ahieve my goal...is this possible?
Re: Outer Join Question [message #196176 is a reply to message #196171] Wed, 04 October 2006 07:17 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Doesn't seem to like it if I put a (+) after the to_char, but you can easily turn the query round:

SELECT STREETS.STREET, STREETS.CITY, STREETS.REGION, DATE_TIME.DATE, TRAFFIC.CARS_IN_HOUR
FROM DATE_TIME, STREETS, TRAFFIC  TRAFFIC
WHERE TRAFFIC.DATE = DATE_TIME.DATE (+)
AND TRAFFIC.STREET (+) = STREETS.STREET AND TRAFFIC.CITY (+) = STREETS.CITY
AND STREETS.REGION = 'SE'
AND TRAFFIC.DATE (+)  = TO_DATE('03-10-2006', 'DD-MM-YYYY')


Actually, putting the to_char round traffic.date is a bad idea anyway, because it might stop any indexes on it working.

I've always hated tools like Business Objects that put impose their own restrictions on what you can write in SQL.

[Updated on: Wed, 04 October 2006 07:18]

Report message to a moderator

Re: Outer Join Question [message #196177 is a reply to message #196121] Wed, 04 October 2006 07:25 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You can even include it in the TO_CHAR, if you really have to:

SELECT STREETS.STREET, STREETS.CITY, STREETS.REGION, DATE_TIME.DATE, TRAFFIC.CARS_IN_HOUR
FROM DATE_TIME, STREETS, TRAFFIC
WHERE TRAFFIC.DATE = DATE_TIME.DATE (+)
AND TRAFFIC.STREET (+) = STREETS.STREET AND TRAFFIC.CITY (+) = STREETS.CITY
AND STREETS.REGION = 'SE'
AND TO_CHAR (TRAFFIC.DATE (+), 'DD-MM-YYYY') = '03-10-2006'

[Updated on: Wed, 04 October 2006 07:26]

Report message to a moderator

Previous Topic: substr
Next Topic: Cant Find Missing parenthesis
Goto Forum:
  


Current Time: Wed Dec 07 14:57:26 CST 2016

Total time taken to generate the page: 0.13606 seconds