Home » SQL & PL/SQL » SQL & PL/SQL » joining conditions
joining conditions [message #346979] Wed, 10 September 2008 05:22 Go to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

is there any way of joining three tables filtering data where we don't need to use any equality sign in conditions??

thanks
Re: joining conditions [message #346983 is a reply to message #346979] Wed, 10 September 2008 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain your question.
Cartesian product?

Regards
Michel
Re: joining conditions [message #346985 is a reply to message #346979] Wed, 10 September 2008 05:45 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Are you talking about ANSI syntax? These dont use '=' sign.
http://oracledoug.com/serendipity/index.php?/archives/933-ANSI-Join-Syntax.html

[Updated on: Wed, 10 September 2008 05:46]

Report message to a moderator

Re: joining conditions [message #346993 is a reply to message #346985] Wed, 10 September 2008 06:22 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:
Are you talking about ANSI syntax? These dont use '=' sign.



Not true unless there is no common column(say deptno) exists in the joining tables


Regards,
Oli

[Updated on: Wed, 10 September 2008 06:24]

Report message to a moderator

Re: joining conditions [message #346998 is a reply to message #346993] Wed, 10 September 2008 06:46 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Actually That is the case only for 'Natural Join'
There are still other types in it . Using,On,Cross Join , Inner,Outer . All these are ANSI compliant.

http://www.oracle-base.com/articles/9i/ANSIISOSQLSupport.php#Joins

Probably the URL i posted earlier dint talk about that Razz

Regards,
Dwarak
Re: joining conditions [message #347010 is a reply to message #346998] Wed, 10 September 2008 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Actually That is the case only for 'Natural Join'

Not true. Cartesian join also does not need any '='.

Regards
Michel

[Updated on: Wed, 10 September 2008 07:32]

Report message to a moderator

Re: joining conditions [message #347027 is a reply to message #347010] Wed, 10 September 2008 08:12 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Hi Michel

Think you have misunderstood my post Smile

Quote:
Actually That is the case only for 'Natural Join'

This was for Olivia's Post
Quote:
Not true unless there is no common column(say deptno) exists in the joining tables


I understand natural join, Using, Cartesian Joins syntax don't need a '=' sign

I had mentioned Cartesian join as Cross join (ANSI term) in my previous post.

Other ANSI joins use '='
Re: joining conditions [message #347065 is a reply to message #347027] Wed, 10 September 2008 09:57 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:

I understand natural join, Using, Cartesian Joins syntax don't need a '=' sign


Thts correct but..use of Natural Join have some demerits.


If the columns you are joining are not common you can't use Using clause.

Quote:

Are you talking about ANSI syntax? These dont use '=' sign.



This means that ANSI join doesn't use '='. isn't it?

And I said, tht's not true Smile


Regards,
Oli

[Updated on: Wed, 10 September 2008 10:02]

Report message to a moderator

Re: joining conditions [message #347083 is a reply to message #346979] Wed, 10 September 2008 10:43 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

hi thanks all of u for replying my query.

i think natural join condition can be used for my query but does this filter data for three specific tables?
where i need to get three columns from two different tables.
Re: joining conditions [message #348163 is a reply to message #346979] Mon, 15 September 2008 18:19 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

i need to get agent id, information_id and gleaned date from the tables information, info_access and agents where
information table got agent_id,information_id,gleaned date and info_access got agent_id,information_id and other field names and agents table has got agent_id.

the query should return those agents who accessed information system. and the syntax should not be having any equality sign in their joining conditions.

i have tried both of the following which are providing different result sets.

select
ia.agent_id,
i.information_id,
i.gleaned_date
from
info_access ia
inner join information i on
i.information_id=ia.information_id
;

select
agent_id,
information_id,
gleaned_date
from
info_access
natural join
information
;

any kind of help would be appreciated.
thanks
Re: joining conditions [message #348167 is a reply to message #346979] Mon, 15 September 2008 18:23 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: joining conditions [message #348210 is a reply to message #348163] Tue, 16 September 2008 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No one can help without relevant information like table description, for instance.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: joining conditions [message #348301 is a reply to message #346979] Tue, 16 September 2008 06:48 Go to previous messageGo to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

hi, tables information are listed below:

i need to get agent id, information_id and gleaned date from the following tables:

INFORMATION
(
information_id PK,
agent_id FK,
gleaned date,
security_level,
gleaned_date,
description
)

INFO_ACCESS
(
agent_id FK,
information_id FK,
)

agents
(
agent_id PK,
first_name,
last_name,
code_name,
security_level
)

the query should return those agents who accessed information system. and the syntax should not be having any equality sign in their joining conditions.

i have tried both of the following which are providing different result sets.


SELECT ia.Agent_Id,
i.Information_Id,
i.Gleaned_Date
FROM Info_Access ia
INNER JOIN Information i
ON i.Information_Id = ia.Information_Id;

SELECT Agent_Id,
Information_Id,
Gleaned_Date
FROM Info_Access
NATURAL JOIN Information;

thanks
Re: joining conditions [message #348302 is a reply to message #348301] Tue, 16 September 2008 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Follow the guidelines.

Regards
Michel
Re: joining conditions [message #348343 is a reply to message #348301] Tue, 16 September 2008 08:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ukdas wrote on Tue, 16 September 2008 07:48

and the syntax should not be having any equality sign in their joining conditions.

SELECT ia.Agent_Id,
i.Information_Id,
i.Gleaned_Date
FROM Info_Access ia
INNER JOIN Information i
ON i.Information_Id = ia.Information_Id;



Dead
Re: joining conditions [message #348355 is a reply to message #346979] Tue, 16 September 2008 09:02 Go to previous message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

i just mentioned that joining one to compare the results of two different joining conidition where both giving different result set.
Previous Topic: union query not returning expected result
Next Topic: Create New User
Goto Forum:
  


Current Time: Sat Dec 03 01:12:37 CST 2016

Total time taken to generate the page: 0.05917 seconds