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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 inner join vs. in

Author  Topic 

kt
Yak Posting Veteran

88 Posts

Posted - 2013-02-13 : 09:51:14
select name,phone, mes
from dbo.tbl1 a
where 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 able
to 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.mes
FROM
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
Go to Top of Page

kt
Yak Posting Veteran

88 Posts

Posted - 2013-02-13 : 10:14:43
thanks, but why you have SELECT TOP (1) mes ?
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -