Re: Number of joins in the query

The minimum number of equi-joins (a = a) is the number of tables - 1. For 3 tables, a minimum of 2 join conditions are needed. Additional joins are needed for composite values (relationship defined by multiple columns) or theta joins (where the join is on a range of values).

Krishnaswamy, Ranganath wrote:

>Dear List,
>
> I have a basic doubt about the number of joins I should have. Say,
>I have three tables by name station, station_restriction and
>stn_rstcn_to_frm with the following structure:
>
>Station Station_restriction stn_rstcn_to_frm
>---------- ----------------------------
>-------------------------
>stn_key(PK) stn_rstcn_key(PK) stn_rstcn_key(FK)
>station_code stn_key(FK) stn_key(FK)
>station_name restricted_position country
>
> If I have to select data from all the three tables should I have two
>joins or three joins?
>
> If I have two joins, I would have
>
> Station.stn_key=Station_restriction.stn_key and
>Station_restriction.stn_rstcn_key = stn_rstcn_to_frm.stn_rstcn_key.
>
> If I have three joins I would have
>
> Station.stn_key=Station_restriction.stn_key and
>Station_restriction.stn_rstcn_key = stn_rstcn_to_frm.stn_rstcn_key
> and Station.stn_key = stn_rstcn_to_frm.stn_key
>
> Can anybody let me know as to how many joins should I have so that I
>don't get any cartesian product in the result set?
>
> I am sorry if the question sounds trivial to someone but I got this
>basic doubt while writing a complex query for a multi-table join.
>
> Any help in this regard is very much appreciated.
>
>Thanks and Regards,
>
>Ranganath
>
>
>WARNING: The information in this message is confidential and may be legally
>privileged. It is intended solely for the addressee. Access to this message
>by anyone else is unauthorised. If you are not the intended recipient, any
>disclosure, copying, or distribution of the message, or any action or
>omission taken by you in reliance on it, is prohibited and may be unlawful.
>Please immediately contact the sender if you have received this message in
>error. Thank you.
>
>

-- 
Daniel W. Fink
http://www.optimaldba.com

RMOUG Training Days March 5 & 6, 2003 Denver, CO
IOUG-A Live! April 27 - May 1, 2003 Orlando, FL




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).