Re: Tieing table together

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Fri, 08 Dec 2000 15:41:26 GMT
Message-ID: <3a30fecf.1522584703@news.alt.net>

On Fri, 8 Dec 2000 08:27:45 -0500, "MSN" <cyberming_at_hotmail.com> wrote:

>I believe brian you need AND C.A_Id = D.A_Id
>in your select statement to prevent cartesian join b/w C and D
>

I don't believe it would as they are both tied to A. When the appropriate record in A is found it limits the rows in both C and D to 1 column a piece, and D, having a reference to B, therefore limits B to only one column.

As an example:

CREATE TABLE A (Id NUMBER(8) PRIMARY KEY, Name VARCHAR2(10) NOT NULL);
CREATE TABLE B (Id NUMBER(8) PRIMARY KEY, Name VARCHAR2(10) NOT NULL);

CREATE TABLE C (Id  NUMBER(8) PRIMARY KEY, A_Id NUMBER(8) NOT NULL,
Name  VARCHAR2(10) NOT NULL,  FOREIGN KEY (A_Id) REFERENCES A);
CREATE TABLE D (Id  NUMBER(8) PRIMARY KEY, A_Id NUMBER(8) NOT NULL,

B_Id NUMBER(8) NOT NULL, Name VARCHAR2(10) NOT NULL, FOREIGN KEY (A_Id) REFERENCES A, FOREIGN KEY (B_Id) REFERENCES B);         

INSERT INTO A VALUES(1,'A1');
INSERT INTO A VALUES(2,'A2'); INSERT INTO B VALUES(1,'B1');
INSERT INTO B VALUES(2,'B2');

INSERT INTO C VALUES(1, 1, 'C1');
INSERT INTO C VALUES(2, 1, 'C2');
INSERT INTO C VALUES(3, 2, 'C3');
INSERT INTO C VALUES(4, 2, 'C4');

INSERT INTO D VALUES(1, 1, 1, 'D1');
INSERT INTO D VALUES(2, 1, 2, 'D2');

INSERT INTO D VALUES(3, 2, 1, 'D3');
INSERT INTO D VALUES(4, 2, 2, 'D4'); Here's the first query

SELECT

	A.Name A_Name,
	B.Name B_Name,
	C.Name C_Name,
	D.Name D_Name
FROM
	A,
	B,
	C,
	D
WHERE
	A.Id = C.A_Id
   AND	A.Id = D.A_Id
   AND	B.Id = D.B_Id;

It returns only eight rows, not a Cartesian product, which would be sixteen rows.

The query with the 'extra' where clause returns the same amount of rows.

SELECT

	A.Name A_Name,
	B.Name B_Name,
	C.Name C_Name,
	D.Name D_Name
FROM
	A,
	B,
	C,
	D
WHERE
	A.Id = C.A_Id
   AND	A.Id = D.A_Id
   AND	B.Id = D.B_Id

   AND C.A_Id = D.A_Id;

Cleanup:

DROP TABLE A;
DROP TABLE B;
DROP TABLE C;
DROP TABLE D;

Brian Received on Fri Dec 08 2000 - 09:41:26 CST