SQL Server many to many without primary key relation

Question

I'm in T-SQL trouble. The case has two tables, expect left join one row from other table and join not same row for result.

Edit case : tabls

My Code:

SELECT T1.ID,T2.BID,T2.FID,T3.FID,T3.PID,T4.PID,T4.Name,T5.FID
FROM T1
INNER JOIN T2 ON T2.BID = T1.ID
INNER JOIN T3 ON T3.FID = T2.FID
INNER JOIN T4 ON T4.PID = T3.PID
OUTER APPLY(SELECT TOP(1) T5.*,T3.PID FROM T5 INNER JOIN T3 ON T3.FID = T5.FID WHERE T5.BID = T2.BID AND T2.IsDone
 = 1 AND T3.PID = T3.PID ORDER BY NEWID()) T5

i want T5.FID no repetition

i got this T5.FID two times

result

there is tables DDL and sample insert data.

CREATE TABLE [dbo].[T1](
    [ID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T2](
    [BID] [int] NOT NULL,
    [FID] [int] NOT NULL,
    [IsDone] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T3](
    [FID] [int] NOT NULL,
    [PID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T4](
    [PID] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T5](
    [FID] [int] NOT NULL,
    [BID] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[T1] ([ID]) VALUES (1);
INSERT [dbo].[T1] ([ID]) VALUES (2);
INSERT [dbo].[T1] ([ID]) VALUES (3);
INSERT [dbo].[T1] ([ID]) VALUES (4);
INSERT [dbo].[T2] ([BID], [FID], [IsDone]) VALUES (1, 12, 1);
INSERT [dbo].[T2] ([BID], [FID], [IsDone]) VALUES (2, 13, 1);
INSERT [dbo].[T2] ([BID], [FID], [IsDone]) VALUES (2, 14, 1);
INSERT [dbo].[T2] ([BID], [FID], [IsDone]) VALUES (2, 15, 1);
INSERT [dbo].[T2] ([BID], [FID], [IsDone]) VALUES (2, 16, 0);
INSERT [dbo].[T2] ([BID], [FID], [IsDone]) VALUES (3, 17, 0);
INSERT [dbo].[T2] ([BID], [FID], [IsDone]) VALUES (4, 18, 0);
INSERT [dbo].[T3] ([FID], [PID]) VALUES (12, 2);
INSERT [dbo].[T3] ([FID], [PID]) VALUES (13, 3);
INSERT [dbo].[T3] ([FID], [PID]) VALUES (14, 3);
INSERT [dbo].[T3] ([FID], [PID]) VALUES (15, 3);
INSERT [dbo].[T3] ([FID], [PID]) VALUES (16, 3);
INSERT [dbo].[T3] ([FID], [PID]) VALUES (17, 4);
INSERT [dbo].[T3] ([FID], [PID]) VALUES (18, 5);
INSERT [dbo].[T3] ([FID], [PID]) VALUES (19, 3);
INSERT [dbo].[T3] ([FID], [PID]) VALUES (20, 3);
INSERT [dbo].[T3] ([FID], [PID]) VALUES (21, 3);
INSERT [dbo].[T4] ([PID], [Name]) VALUES (2, N'A');
INSERT [dbo].[T4] ([PID], [Name]) VALUES (3, N'B');
INSERT [dbo].[T4] ([PID], [Name]) VALUES (4, N'C');
INSERT [dbo].[T4] ([PID], [Name]) VALUES (5, N'D');
INSERT [dbo].[T5] ([FID], [BID]) VALUES (19, 2);
INSERT [dbo].[T5] ([FID], [BID]) VALUES (20, 2);
INSERT [dbo].[T5] ([FID], [BID]) VALUES (21, 2);

Please post sample data and expected results as text instead of images. Preferably in DML so we can easily recreate it on our end. — squillman, Jul 21 '21 at 13:08
Why do you have multiple **identical** rows in `Table1`? That's normally a sign of a design flaw. — Thom A, Jul 21 '21 at 13:10
@Larnu it's second development project, actually has many tables, not two tables. i want use left join and result is not same row. — Delevin, Jul 21 '21 at 13:53
So in SOF, it is very important to under "how to ask a good question". First, don't paste IMAGES of source code. Nobody is going to handtype all the text from an image. Second. Create unambiguous table DDL and sample insert data. Third, do a "what I expect" (rows of data) , not just a description. Those are some of my hope that helps hints. — granadaCoder, Jul 21 '21 at 14:17
@granadaCoder sorry, I put a wrong example in first time, I'm delaying everyone, please forgive me, updated now. — Delevin, Jul 21 '21 at 14:39

Thom A · Answer 1 · 2021-07-21T13:20:49.963

0

Looks like you could just do DISTINCT due to the data in Table1 all being completely identical.

SELECT DISTINCT
       T1.AID,
       T1.[Name],
       T2.Property
FROM dbo.Table1 T1
     JOIN dbo.Table2 T2 ON T1.AID = T2.AID;

I, personally, would suggest that having multiple completely identical rows in a single table generally means you have a data issue that needs addressing. If do need to DELETE the duplicates, I suggest having a look at How to delete duplicate rows in SQL Server?. Then you can remove the expensive DISTINCT operator.

edited Jul 21 '21 at 13:20

answered Jul 21 '21 at 13:14

Thom A

88,727
11
45
75

sorry，the case just an example, actually it's complicated, not two tables, so ,how to do this use join? – Delevin Jul 21 '21 at 13:52
*"how to do this use join"* It's just a simple `INNER JOIN`. If you don't understand `JOIN`s you need to learn the language, @Delevin , they are a fundamental part of the language. – Thom A Jul 21 '21 at 13:59
sorry, I put a wrong example in first time, I'm delaying everyone, please forgive me, updated now. – Delevin Jul 21 '21 at 14:39

SQL Server many to many without primary key relation

1 Answers1