Duplicate in join
-
Hi, This must be real simple, but I'm having a blackout. I have two tables: Names:
ID Name
1 John
2 Peter
3 Carl
4 BernardNicknames:
ID NameID NickName
1 1 Bonny
2 1 Bobby
3 1 Jo
4 2 Pete
5 3 Bab
6 3 FooNow I have a search running, which looks for a certain string in both the nickname field as well as in the name field. But it should return only 1 record per name (doesn't matter which nickname), so if the seachstring would be 'b%' it would return the following.
NameID NickNameID Name Nickname
4 null Bernard null
1 1 John Bonny
3 5 Bernard BabI fall on the distinct record thing for the nicknames, I have a dirty subselect, but since the query is actually much bigger, and the table contains 100.000 records, it isn't the right solution. Does anybody have the correct solution to this issue?
-
Hi, This must be real simple, but I'm having a blackout. I have two tables: Names:
ID Name
1 John
2 Peter
3 Carl
4 BernardNicknames:
ID NameID NickName
1 1 Bonny
2 1 Bobby
3 1 Jo
4 2 Pete
5 3 Bab
6 3 FooNow I have a search running, which looks for a certain string in both the nickname field as well as in the name field. But it should return only 1 record per name (doesn't matter which nickname), so if the seachstring would be 'b%' it would return the following.
NameID NickNameID Name Nickname
4 null Bernard null
1 1 John Bonny
3 5 Bernard BabI fall on the distinct record thing for the nicknames, I have a dirty subselect, but since the query is actually much bigger, and the table contains 100.000 records, it isn't the right solution. Does anybody have the correct solution to this issue?
-
You say that the select should return only one nickname. In your example, why did the select return Bonny for Bernard, not Bobby. Is there somekind of logic or is it simply random.
The need to optimize rises from a bad design.My articles[^]
-
Okey, then you could use normal outer join between the tables and for example check that there's no greater nicknameid for the same name (using for example correlated not exists clause).
The need to optimize rises from a bad design.My articles[^]
-
Hi, This must be real simple, but I'm having a blackout. I have two tables: Names:
ID Name
1 John
2 Peter
3 Carl
4 BernardNicknames:
ID NameID NickName
1 1 Bonny
2 1 Bobby
3 1 Jo
4 2 Pete
5 3 Bab
6 3 FooNow I have a search running, which looks for a certain string in both the nickname field as well as in the name field. But it should return only 1 record per name (doesn't matter which nickname), so if the seachstring would be 'b%' it would return the following.
NameID NickNameID Name Nickname
4 null Bernard null
1 1 John Bonny
3 5 Bernard BabI fall on the distinct record thing for the nicknames, I have a dirty subselect, but since the query is actually much bigger, and the table contains 100.000 records, it isn't the right solution. Does anybody have the correct solution to this issue?
gnjunge wrote:
I have a dirty subselect
I hope this T-SQL is not "dirty" but I couldn't make shorter,I hope too this T-SQL will help you.
select id, (select top 1 A.ID from nicknames as A where A.nameID = names.ID) as NicknameID, name, (select top 1 A.nickname from nicknames as A where A.nameID = names.ID) as Nickname from names where (names.name like 'b%') or names.id in (select C.nameid from nicknames as C where C.nickname like 'b%' ) </code> Result is : `1 1 John Bonny 3 5 Carl Bab 4 NULL Bernard NULL` * * * I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. -
gnjunge wrote:
I have a dirty subselect
I hope this T-SQL is not "dirty" but I couldn't make shorter,I hope too this T-SQL will help you.
select id, (select top 1 A.ID from nicknames as A where A.nameID = names.ID) as NicknameID, name, (select top 1 A.nickname from nicknames as A where A.nameID = names.ID) as Nickname from names where (names.name like 'b%') or names.id in (select C.nameid from nicknames as C where C.nickname like 'b%' ) </code> Result is : `1 1 John Bonny 3 5 Carl Bab 4 NULL Bernard NULL` * * * I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. -
This seems like it would be very slow, or isn't it? I guess in the end the names table would contain around 10.000 records,whereas the nicknames table would contain around 50.000
gnjunge wrote:
This seems like it would be very slow, or isn't it?
Did you try to run in your real data and to see if is it slow? For data which you post,query is not slow.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.