Query with Left Join without returning duplicate value

Question

Query with Left Join without returning duplicate value

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

I have the table tableA and tableB .

I'm making a select of all fields from both tables by joining them together with left Join

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

But the relationship is from 1 to N, in the 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 not use Distinct , Group By because there are more than 100 fields.

Something similar is coming with;

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

sql sql-server

asked by anonymous 18.10.2018 / 22:05

2 answers

How to extract a binary from a string? DTO with List using JpaCriteria

score 0 · Answer 1

Considering only the data presented in the question, you can group all the returns in a 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;

Running Example

score 0 · Answer 2

As you did not provide sample data and expected result, just for fun I propose this example to give you a hint on how to achieve this using the 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 is a live demo

db fiddle demo