Derived Columns Join. - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Derived Columns Join.

Author

Topic

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-12-06 : 01:17:03

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

30421 Posts

Posted - 2006-12-06 : 01:29:19

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

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-12-06 : 01:41:14

[code]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[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-06 : 01:44:36

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

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-12-06 : 01:52:32

Your are RIGHT, PESO.
I need to take care about this.
Thanks for you help once again.
Kind Regards,
Gurpreet S. Gill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources