SQL help with joining tables

Don75 81 Reputation points
2020-12-02T16:06:34.223+00:00

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
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,565 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2020-12-02T19:18:01.557+00:00
    ;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

    1 person found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2020-12-02T16:50:21.39+00:00
    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';
    
    0 comments No comments

  2. Don75 81 Reputation points
    2020-12-02T18:22:32.207+00:00

    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.

    44496-image.png

    0 comments No comments

  3. Don75 81 Reputation points
    2020-12-02T19:42:09.807+00:00

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

    0 comments No comments