SQL Server How to avoid duplicate rows in two tables without distinct in SQL?

moiiocjp 于 2023-05-05 发布在其他

关注(0)|答案(2)|浏览(136)

I am having a hard time understanding the concepts of join when there are extra level of details on second table. I have two tables as shown here, table 1 and table 2.

Let's say I want to return the DOB from table 2 alongside data from table 1:

select 
    t1.PERSON ID,
    t1.Celeb First Name,
    t1.Celeb Last Name,
    t2.DOB
from
    table 1 as t1
left join 
    table 2 t2 on t1.personid = t2.personid

I get duplicate rows even when I do a group by. What is the solution to only return one row? I don't want to use distinct as that slows my query a lot.

select 
    t1.PERSON ID,
    t1.Celeb First Name,
    t1.Celeb Last Name,
    t2.DOB
from 
    table 1 as t1
left join 
    table 2 t2 on t1.personid = t2.personid
group by
    t1.PERSON ID,
    t1.Celeb First Name,
    t1.Celeb Last Name,
    t2.DOB

sql-server

来源：https://stackoverflow.com/questions/76158112/how-to-avoid-duplicate-rows-in-two-tables-without-distinct-in-sql

2条答案

按热度按时间

0pizxfdo1#

You're getting more than one row per person because there is more than one value for DOB according to your data; Person 1 having a DOB of '1/1/2020' and NULL. You could filter out these rows with the following WHERE clause:

WHERE DOB IS NOT NULL

This will cause problems if you wanted to use the [MOVIE ACCESS] later though.

赞(0）回复(0）举报 2023-05-05

w8ntj3qf2#

Use one of the aggregation functions like max() or min() to retrieve just one row, in your case you will need to use max() :

select 
    t1.PERSON ID,
    t1.Celeb First Name,
    t1.Celeb Last Name,
    max(t2.DOB)
from 
    table 1 as t1
left join 
    table 2 t2 on t1.personid = t2.personid
group by
    t1.PERSON ID,
    t1.Celeb First Name,
    t1.Celeb Last Name

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server How to avoid duplicate rows in two tables without distinct in SQL?

2条答案

相关问题

热门标签

最新问答