SQL help with joining tables

Question

I have 3 sql tables that I am trying to write a query on.
vAD1 ia the main table. This is linked with the other table using field ID1

When I run the following I get one row from the vQR3 table

Select vAD1.,vQR3. from vAD1
join vQR3 on vAD1.ID1 = vQR3.ID1
where vAD1.ID1= 'C001'

However, when I link vPRE table, my data gets duplicated. For every row in vPRE table, data in vQR3 is get duplicated. Is there away to remove the duplicates. I have about 6 tables to link. Then I get the data in table 2, 3, 4 and 5 duplicated in my final query. I am only including 3 tables here to see whether I could have the duplicates removed in this one.

Select vAD1.,vQR3., vPRE.* from vAD1
join vQR3 on vAD1.ID1 = vQR3.ID1
join vPRE on vAD1.ID1 =vPRE.ID1
where vAD1.ID1= 'C001'

CREATE TABLE vAD1(
       [ID1] [varchar](30) NULL,[VID1] [varchar](30) NULL, [F1] [varchar](13) NOT NULL, inv_date [datetime] NULL, LOCATION [varchar](15) NULL,

) 

INSERT INTO vAD1 values ('C001','AB111','AAA','08/15/2020','1234')
INSERT INTO vAD1 values ('C002','AB222','BBB','08/20/2020','12345')
INSERT INTO vAD1 values ('C003','AB333','CCC','08/15/2020','12345')
INSERT INTO vAD1 values ('C004','AB333','DDD','09/01/2020','12345')
INSERT INTO vAD1 values ('C005','AB333','EEE','09/13/2020','12345')
INSERT INTO vAD1 values ('C006','AB444','FFF','08/13/2020','12WW34')
INSERT INTO vAD1 values ('C007','AB555','GGG','08/25/2020','1225SG34')
INSERT INTO vAD1 values ('C008','AB555','GGG','10/23/2020','121111134')
INSERT INTO vAD1 values ('C009','AB777','ASD','09/26/2020','222212345')
INSERT INTO vAD1 values ('C010','AB888','GHT','10/13/2020','1233333345')

CREATE TABLE vQR3(
       [ID1] [varchar](30) NULL, [QR] [varchar](13) NOT NULL, Qdate [datetime] NULL,

) 

INSERT INTO vQR3 values ('C001','Q1','08/15/2020')
INSERT INTO vQR3 values ('C002','Q2','08/15/2020')
INSERT INTO vQR3 values ('C002','Q3','08/15/2020')
INSERT INTO vQR3 values ('C002','Q4','08/15/2020')
INSERT INTO vQR3 values ('C002','Q5','08/15/2020')
INSERT INTO vQR3 values ('C004','Q6','08/15/2020')
INSERT INTO vQR3 values ('C004','Q6','08/15/2020')
INSERT INTO vQR3 values ('C004','Q6','08/15/2020')
INSERT INTO vQR3 values ('C004','Q7','08/15/2020')
INSERT INTO vQR3 values ('C004','Q8','08/15/2020')
INSERT INTO vQR3 values ('C005','Q1','08/15/2020')
INSERT INTO vQR3 values ('C005','Q2','08/15/2020')
INSERT INTO vQR3 values ('C005','Q3','08/15/2020')
INSERT INTO vQR3 values ('C005','Q4','08/15/2020')
INSERT INTO vQR3 values ('C005','Q5','08/15/2020')
INSERT INTO vQR3 values ('C005','Q6','08/15/2020')
INSERT INTO vQR3 values ('C005','Q6','08/15/2020')
INSERT INTO vQR3 values ('C005','Q6','08/15/2020')
INSERT INTO vQR3 values ('C007','Q7','08/15/2020')
INSERT INTO vQR3 values ('C007','Q8','08/15/2020')

CREATE TABLE vPRE(
       [ID1] [varchar](30) NULL, [PRT] [varchar](13) NOT NULL,[PRTD] [varchar](13) NULL, Sdate [datetime] NULL,

) 

INSERT INTO vPRE values ('C001','P001','PRD1','08/15/2020')
INSERT INTO vPRE values ('C001','P001','PRD1','08/15/2020')
INSERT INTO vPRE values ('C001','P002','PRD2','08/15/2020')
INSERT INTO vPRE values ('C001','P003','PRD3','08/15/2020')
INSERT INTO vPRE values ('C001','P004','PRD4','08/15/2020')
INSERT INTO vPRE values ('C002','P001','PRD1','08/15/2020')
INSERT INTO vPRE values ('C002','P002','PRD2','08/15/2020')
INSERT INTO vPRE values ('C003','P003','PRD3','08/15/2020')
INSERT INTO vPRE values ('C004','P004','PRD4','08/15/2020')
INSERT INTO vPRE values ('C004','P001','PRD1','08/15/2020')
INSERT INTO vPRE values ('C004','P002','PRD2','08/15/2020')
INSERT INTO vPRE values ('C005','P003','PRD3','08/15/2020')
INSERT INTO vPRE values ('C005','P004','PRD4','08/15/2020')
INSERT INTO vPRE values ('C006','P001','PRD1','08/15/2020')
INSERT INTO vPRE values ('C006','P002','PRD2','08/15/2020')
INSERT INTO vPRE values ('C006','P003','PRD3','08/15/2020')
INSERT INTO vPRE values ('C009','P004','PRD4','08/15/2020')
INSERT INTO vPRE values ('C009','P004','PRD4','08/15/2020')
INSERT INTO vPRE values ('C009','P004','PRD4','08/15/2020')
INSERT INTO vPRE values ('C010','P004','PRD4','08/15/2020')

drop table vAD1
DROP TABLE vQR3
DROP TABLE vPRE

Accepted Answer

;With cte As
(Select vAD1.ID1, vAD1.VID1, vAD1.F1, vAD1.inv_date, vAD1.LOCATION, 
  /*vQR3.ID1,*/ vQR3.QR, vQR3.Qdate, 
  /*vPRE.ID1,*/ vPRE.PRT, vPRE.PRTD, vPRE.Sdate,
  Row_Number() Over(Partition By vQR3.[ID1], vQR3.[QR], vQR3.Qdate Order By vQR3.ID1) As vAD1rn,
  Row_Number() Over(Partition By vQR3.[ID1], vQR3.[QR], vQR3.Qdate, vPRE.PRT, vPRE.PRTD, vPRE.Sdate Order By vQR3.ID1) As vPRErn
from vAD1
join vQR3 on vAD1.ID1 = vQR3.ID1
join vPRE on vAD1.ID1 =vPRE.ID1
where vAD1.ID1= 'C001')
Select ID1, VID1, F1, LOCATION, 
  Case When vAD1rn = 1 Then QR Else Null End As QR, 
  Case When vAD1rn = 1 Then Qdate Else Null End As Qdate, 
  PRT, PRTD, Sdate
From cte
Where vPRErn = 1
Order By ID1;

Tom

Answer

SELECT DISTINCT vAD1.*, vQR3.*, vPRE.* 
FROM vAD1
JOIN vQR3 ON vAD1.ID1 = vQR3.ID1
JOIN vPRE ON vAD1.ID1 = vPRE.ID1
WHERE vAD1.ID1 = 'C001';

Answer

Thanks for the response. Much appreciated. That was not the result I was expecting.
For ID "C001"
There is 1 record in vQR3
There are 5 records in vPRE
I need to give counts on certain fields. For example, if I were to give a count on vQR3.QR field in my report, it will show as 4, but the actual count for C001 is 1.
I am expecting the following output. Not sure whether this is possible.

Answer

Thanks. Much appreciated. It works. I am going to add the rest of the tables and see how it works.

SQL help with joining tables

3 additional answers