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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Derived Columns Join.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gsgill76
Posting Yak Master

India
137 Posts

Posted - 12/06/2006 :  01:17:03  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
Hi I have

CREATE TABLE [dbo].[table1](
	[FullName] [varchar](40) 
)
CREATE TABLE [dbo].[table2](
	[FirstName] [varchar](20) ,
	[SurName] [varchar](20) 
) 
INSERT INTO [dbo].[table1] ([FullName]) VALUES ('Gurpreet Gill')
INSERT INTO [dbo].[table1] ([FullName]) VALUES ('Dave Sumit')
INSERT INTO [dbo].[table1] ([FullName]) VALUES ('Monty Jocab')
INSERT INTO [dbo].[table2] ([FirstName], [SurName]) VALUES ('Gurpreet', NULL)
INSERT INTO [dbo].[table2] ([FirstName], [SurName]) VALUES ('Dave', 'Sumit')
INSERT INTO [dbo].[table2] ([FirstName], [SurName]) VALUES ('News', 'Java')


SELECT     
LEFT(table1.FullName, CHARINDEX(' ', table1.FullName) - 1) AS FN, 
SUBSTRING(table1.FullName, CHARINDEX(' ', table1.FullName) + 1, 30) AS SN
FROM         
table1 inner JOIN
table2 ON 
LEFT(table1.FullName, CHARINDEX(' ', table1.FullName) - 1) = table2.FirstName 
AND 
SUBSTRING(table1.FullName, CHARINDEX(' ', table1.FullName) + 1, 30) = table2.SurName


I am trying to make a derived columns join.

Is there any other efficient method of doing so (as the substing cost more in the join)

One case more, the First Name must be display if it matches, for this ignore the NULL in the Sun name. As in the Example ‘Gurpreet’, ‘Null’ must be display.

The Left outer join doesn’t help me.

I need a single select statment, no block.

Kind Regards,
Thanks.
Gurpreet S. Gill

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/06/2006 :  01:29:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No changes in execution plan, but in my opinion this is easier to read.
SELECT		t1.FN,
		t1.SN
FROM		(
			select	LEFT(FullName, CHARINDEX(' ', FullName) - 1) AS FN, 
				SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, 30) AS SN
			from	table1
		) t1
inner JOIN	table2 ON table2.FirstName = t1.fn AND table2.SurName = t1.sn


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 12/06/2006 :  01:41:14  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
SELECT		t1.FN,
		t1.SN
FROM		(
			select	LEFT(FullName, CHARINDEX(' ', FullName) - 1) AS FN, 
				SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, 30) AS SN
			from	table1
		) t1
inner JOIN	table2 ON table2.FirstName = t1.fn AND table2.SurName = t1.sn


Thats seems nice, but what about the Special case, i mean for match "FirstName" + NULL, match must display.
Ex:
form my sample data, the output i need

Dave Sumit
Gurpreet NULL

Regards,
Thanks.
Gurpreet S. Gill
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 12/06/2006 :  01:44:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
For this to be a success, you need a primary key on table1 because what there is this record in table1?
INSERT INTO [dbo].[table1] ([FullName]) VALUES ('Gurpreet James')

Then the surname Gurpreet would match twice against table2.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gsgill76
Posting Yak Master

India
137 Posts

Posted - 12/06/2006 :  01:52:32  Show Profile  Click to see gsgill76's MSN Messenger address  Send gsgill76 a Yahoo! Message  Reply with Quote
Your are RIGHT, PESO.
I need to take care about this.
Thanks for you help once again.
Kind Regards,
Gurpreet S. Gill
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.09 seconds. Powered By: Snitz Forums 2000