CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Sunil Acharya
Sunil Acharya

Reputation: 1183

Joining multiple tables results in duplicate rows

There are tow tables (Customer & Feedback) that have different information. The following query is almost correct, except that it results in duplicate rows. I only need unique rows as par customerId and NO for null value.

I tried the GROUP BY clause at the end but it gives error.

Select C.CustomerId, 
       C.FirstName, 
       C.LastName, 
       (SELECT CAST(CASE WHEN F.Id != null or F.Type = 'Query' 
                         THEN 'YES' ELSE 'NO' END AS NVARCHAR(50))) as Query,
       (SELECT CAST(CASE WHEN F.Id != null or F.Type = 'Feedback' 
                         THEN 'YES' ELSE 'NO' END AS NVARCHAR(50))) as Feedback
FROM Customer C
LEFT JOIN Feedback F on  F.CustomerId= C.CustomerId

enter image description here

Select * from Customer

enter image description here

Select * from Feedback

enter image description here

As Result I want to display only single row by customerId and to join the feedback table data like below... enter image description here

Upvotes: 0

Views: 1045

Answers (5)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

You May Try either of the Below

SELECT
    C.*,
    Query = CASE WHEN PVT.Query IS NOT NULL THEN 'Yes' ELSE 'No' END,
    Feedback = CASE WHEN PVT.Feedback IS NOT NULL THEN 'Yes' ELSE 'No' END
    FROM Customer C
        LEFT JOIN  FeedBack
            PIVOT
            (
                MAX(Id)
                FOR
                [Type] IN
                (
                    [Query],[Feedback]
                )
            )Pvt
            ON PVT.CustomerId = c.CustomerId

or Simply

SELECT
    C.*,
    Query = CASE WHEN EXISTS(SELECT 1 FROM FeedBack WHERE CustomerId = C.CustomerId and [Type]='Query') THEN 'Yes' ELSE 'No' END,
    Feedback = CASE WHEN EXISTS(SELECT 1 FROM FeedBack WHERE CustomerId = C.CustomerId and [Type]='Feedback') THEN 'Yes' ELSE 'No' END
    FROM Customer C

To Make it more Dynamic ou may Try this

DECLARE @SQL VARCHAR(MAX)

;WITH CTE
AS
(
    SELECT
        RN = ROW_NUMBER() OVER(PARTITION BY [Type] ORDER BY [Type]),
        QRY = LTRIM(RTRIM([Type]))+' = CASE WHEN EXISTS(SELECT 1 FROM FeedBack WHERE CustomerId = C.CustomerId and [Type]='''+LTRIM(RTRIM([Type]))+''') THEN ''Yes'' ELSE ''No'' END'
        FROM FeedBack
)
SELECT
    @SQL = 'SELECT
    C.*'
    +SUBSTRING(','+L.List,1,LEN(L.List)-1)
    +' FROM Customer C'
    FROM 
    (
        SELECT
            QRY + ', ' [text()]
            FROM CTE
                WHERE RN = 1
                FOR XML PATH('')
    )L(List)

EXEC(@SQL)

Please Refer this Sqlfiddle for detailed example

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37500

Try this:

select c.CustomerId,
       c.FirstName,
       c.LastName,
       case when q.CustomerId is null then 'NO' else 'YES' end Query,
       case when f.CustomerId is null then 'NO' else 'YES' end Feedback,
from Customers c
left join (select customerId from Feedback where Type = 'Query' ) q on c.CustomerId = q.CustomerId
left join (select customerId from Feedback where Type = 'Feedback' ) f on c.CustomerId = q.CustomerId

Upvotes: 0

Fahmi
Fahmi

Reputation: 37483

Try below query using subquery:

    select *,case when query=1 then 'Yes' else 'No' end as query, 
case when feedback=1 then 'Yes' else 'No' end as feedbackfrom
(select CustomerId, firstname,lastname,
        sum(CASE WHEN Type = 'Query' 
             THEN 1 ELSE 0 END) as Query,

        sum(CASE WHEN Type = 'Feedback' 
             THEN 1 ELSE 0 END)  as Feedback from Customer C
        LEFT JOIN Feedback F on  F.CustomerId= C.CustomerId
group by CustomerId, firstname,lastname)a

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32011

use case when 

Select C.CustomerId, 
       C.FirstName, 
       C.LastName, 
       case when sum(case when F.Type = 'Query' then 1 else 0 end) > 0  then 'Yes' else 'NO' end as Query,
    case when   sum(case when F.Type = 'Feedback' then 1 else 0 end) > 0 then 'Yes' else 'NO' End as Feedback
FROM Customer C
LEFT JOIN Feedback F on  F.CustomerId= C.CustomerId
GROUP BY C.CustomerId, C.FirstName, C.LastName

Upvotes: 0

juergen d
juergen d

Reputation: 204904

Select C.CustomerId, 
       C.FirstName, 
       C.LastName, 
       sum(case when F.Type = 'Query' then 1 else 0 end) > 0 as Query,
       sum(case when F.Type = 'Feedback' then 1 else 0 end) > 0 as Feedback
FROM Customer C
LEFT JOIN Feedback F on  F.CustomerId= C.CustomerId
GROUP BY C.CustomerId, C.FirstName, C.LastName

Upvotes: 1

Related Questions