StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL cartesian join problem
text
Body
copied!<p>I have three tables </p> <ul> <li>A: A.pID primary key, A.Name nvarchar(250)</li> <li>B: B.pID primary key, B.Name nvarchar(250)</li> <li>C: C.pID primary key, C.Name nvarchar(250)</li> </ul> <p>There is a m to n relation between A and B (table <code>lA_B</code> with primary key <code>lA_B.pID</code> and <code>.pInstanceA</code> Foreign key to table A and <code>.pInstanceB</code> Foreign key to table B)</p> <p>There is a m to n relation between A and C (<code>table lA_C</code> with primary key <code>lA_C.pID</code> and <code>.pInstanceA</code> Foreign key to table A and <code>.pInstanceB</code> Foreign key to table C)</p> <ul> <li>A1 is in relation with B1, B2 and C1</li> <li>A2 is in relation with B3 and C2, C3</li> <li>A3 is in relation with B4</li> <li>A4 is in relation with C4</li> <li>A5 has no relation</li> </ul> <p>Here is my SQL:</p> <pre><code>CREATE TABLE [dbo].[A]( [pID] [bigint] NOT NULL, [Name] [nvarchar](250) NULL ) CREATE TABLE [dbo].[B]( [pID] [bigint] NOT NULL, [Name] [nvarchar](250) NULL ) CREATE TABLE [dbo].[C]( [pID] [bigint] NOT NULL, [Name] [nvarchar](250) NULL) CREATE TABLE [dbo].[lA_B]( [pID] [bigint] NOT NULL, [pInstanceA] [bigint] NULL, [pInstanceB] [bigint] NULL ) CREATE TABLE [dbo].[lA_C]( [pID] [bigint] NOT NULL, [pInstanceA] [bigint] NULL, [pInstanceB] [bigint] NULL ) INSERT INTO [dbo].[A] ([pID] ,[Name]) VALUES (1,'A1') INSERT INTO [dbo].[A] ([pID] ,[Name]) VALUES (2,'A2') INSERT INTO [dbo].[A] ([pID] ,[Name]) VALUES (3,'A3') INSERT INTO [dbo].[A] ([pID] ,[Name]) VALUES (4,'A4') INSERT INTO [dbo].[A] ([pID] ,[Name]) VALUES (5,'A5') INSERT INTO [dbo].[B] ([pID] ,[Name]) VALUES (1,'B1') INSERT INTO [dbo].[B] ([pID] ,[Name]) VALUES (2,'B2') INSERT INTO [dbo].[B] ([pID] ,[Name]) VALUES (3,'B3') INSERT INTO [dbo].[B] ([pID] ,[Name]) VALUES (4,'B4') INSERT INTO [dbo].[C] ([pID] ,[Name]) VALUES (1,'C1') INSERT INTO [dbo].[C] ([pID] ,[Name]) VALUES (2,'C2') INSERT INTO [dbo].[C] ([pID] ,[Name]) VALUES (3,'C3') INSERT INTO [dbo].[C] ([pID] ,[Name]) VALUES (4,'C4') INSERT INTO [dbo].[lA_B] ([pID],[pInstanceA],[pInstanceB]) VALUES (1,1,1) INSERT INTO [dbo].[lA_B] ([pID],[pInstanceA],[pInstanceB]) VALUES (2,1,2) INSERT INTO [dbo].[lA_B] ([pID],[pInstanceA],[pInstanceB]) VALUES (3,2,3) INSERT INTO [dbo].[lA_B] ([pID],[pInstanceA],[pInstanceB]) VALUES (4,3,4) INSERT INTO [dbo].[lA_C] ([pID],[pInstanceA],[pInstanceB]) VALUES (1,1,1) INSERT INTO [dbo].[lA_C] ([pID],[pInstanceA],[pInstanceB]) VALUES (2,2,2) INSERT INTO [dbo].[lA_C] ([pID],[pInstanceA],[pInstanceB]) VALUES (3,2,3) INSERT INTO [dbo].[lA_C] ([pID],[pInstanceA],[pInstanceB]) VALUES (4,4,4) </code></pre> <p>this query:</p> <pre><code>SELECT A.Name AS A, B.Name AS B, C.Name AS C FROM A left JOIN lA_B ON (A.pID = lA_B.pInstanceA) left JOIN B ON (B.pID = lA_B.pInstanceB) left JOIN lA_C ON (A.pID = lA_C.pInstanceA) left JOIN C ON (C.pID = lA_C.pInstanceB) </code></pre> <p>returns</p> <pre> A1 B1 C1 A1 B2 C1 A2 B3 C2 A2 B3 C3 A3 B4 NULL A4 NULL C4 A5 NULL NULL </pre> <p>And now the question :-) how to query to receive</p> <pre> A1 B1 NULL A1 B2 NULL A1 NULL C1 A2 B3 NULL A2 NULL C2 A2 NULL C3 A3 B4 NULL A4 NULL C4 A5 NULL NULL </pre> <p>The problem is that when I make the join both with B and with C the result has all the combinations of B C. How can I eliminate this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload