Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
kt
Yak Posting Veteran
88 Posts |
Posted - 2013-02-13 : 09:51:14
|
select name,phone, mes from dbo.tbl1 awhere id in(select id from tb2)for my case, i can't do the inner join because duplicate records. The results is displayed name, phone. I need to do the way i have here but the thing is i want to ableto display the mes but this field is from the tbl 2 tb2 table. How can i have the mes from the select list ?thanks |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-13 : 10:08:25
|
If there are multiple records in tb2 for a single id, that means there are multiple values of mes as well. Which of those do you want to display?SELECT a.Name, a.phone, b.mesFROM dbo.tbl1 a CROSS APPLY ( SELECT TOP (1) mes FROM tb2 b WHERE b.id = a.id ORDER BY mes -- change order by to what you need ) b |
|
|
kt
Yak Posting Veteran
88 Posts |
Posted - 2013-02-13 : 10:14:43
|
thanks, but why you have SELECT TOP (1) mes ? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-13 : 10:26:05
|
If you have more than one row for a given id, do you want to display all of those rows? From what I understood, you did not. You wanted only one row to be displayed. Hence the TOP(1). The TOP (1) will apply to EACH id individually. Run the code as it is, then run it again after removing the TOP (1) construct, and you will see the difference. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-13 : 12:14:34
|
If James suggestion doesn't work for you help us to help you by providing sample data (in a consumable format, i.e. DDL and DML) and expected output. It makes things much eaiser for everyone. Here are some links to help you prepare that invformation for posting questions:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
|
|
|
|