Query with Left Join without returning duplicate value

Asked

Viewed 700 times

2

I have the table tableA and tableB.

I’m making a select of all fields of both tables joining the same by left Join

select * from tableA
left join tableB on tableA.id = tableB.id

But the relationship is 1 to N, in case I have 1 record in A and several in B.

How do I return ALL fields of the 2 tables without repeating? I can’t use Distinct, Group By because there are more than 100 fields.

Is coming something similar with;

Conta    ItemId
000001    PT-BR
000001    EN
000001    USA
000002    PR-BR
sql sql-server

  • And what would be the desired result? If the ratio is 1:N, how do you want the answer?

  • I need you to show only 1 time the Itemid Account is not relevant to my query.

  • 1

    What the distinct has to do with more than 100 fields? If the Itemid field is not relevant, just make a select distinct conta

  • 1

    If the field is not relevant why you included it in the query?

  • I need all fields of all tables A and B, the main point is the account with the other information.. which are various columns.

  • We need DDL, sample data and expected results.

Show 1 more comment

2 answers

0

Considering only the data presented in the question, you can group all returns in one column using Stuff():

SELECT distinct
    id,
    stuff((
        select cast(',' as varchar(max)) + U.coluna
        from (select * from tableA left join tableB on tableA.id = tableB.id) U
        WHERE U.id = G.id
        for xml path('')), 1, 1, '') AS coluna
FROM
    (select * from tableA left join tableB on tableA.id = tableB.id) G;

Working example

0

Since you did not provide sample data and expected result, just for fun I propose this example to give a hint on how to achieve this using the function STUFF()

CREATE TABLE Teachers
(
  TeacherID INT PRIMARY KEY NOT NULL,
  TeacherName VARCHAR(45) NOT NULL
);

CREATE TABLE Classes
(
  ClassID INT PRIMARY KEY NOT NULL,
  Class VARCHAR(45) NOT NULL,
  MainTeacher INT NOT NULL,
  CONSTRAINT FK_Classes_MainTeacher FOREIGN KEY (MainTeacher) REFERENCES Teachers (TeacherID)
);

INSERT INTO Teachers (TeacherID, TeacherName) VALUES
(1, 'Teacher1'),
(2, 'Teacher2');

INSERT INTO Classes (ClassID, Class, MainTeacher) VALUES
(1, 'Class1', 1),
(2, 'Class2', 1),
(3, 'Class3', 2);

SELECT T.*,
       STUFF(
               (
                 SELECT DISTINCT ',' + Class
                 FROM Classes C
                 WHERE C.MainTeacher = T.TeacherID
                 FOR XML PATH('')
               ), 1, 1, ''
            ) ControlledClasses
FROM Teachers T ;

Results:

+-----------+-------------+-------------------+
| TeacherID | TeacherName | ControlledClasses |
+-----------+-------------+-------------------+
|         1 | Teacher1    | Class1,Class2     |
|         2 | Teacher2    | Class3            |
+-----------+-------------+-------------------+

Here’s a live demo

db<>fiddle demo

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.