SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 inner join vs. in		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kt
Starting Member

38 Posts
Posted - 02/13/2013 :  09:51:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 02/13/2013 :  10:08:25  Show Profile  Reply with Quote
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
Starting Member

38 Posts
Posted - 02/13/2013 :  10:14:43  Show Profile  Reply with Quote
thanks, but why you have SELECT TOP (1) mes ?
Edited by - kt on 02/13/2013 10:28:08
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 02/13/2013 :  10:26:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3829 Posts
Posted - 02/13/2013 :  12:14:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000