top 1 on inner join with function

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 2008 Forums

Transact-SQL (2008)

top 1 on inner join with function

Author

Topic

DazlerD
Starting Member

8 Posts

Posted - 2012-01-16 : 12:19:50

Hi all

Im trying to link two tables but they dont have a direct link. I have to link them on surname, address1-5, postcode using a built in sql function. The contact table already has a matchcode but the Landing page doesnt.

Here's what i've got:


select c.Title, c.Forename,c.Surname, c.Address1, c.Address2, 
	c.Address3, c.Address4, c.Address5, c.Town, c.Postcode,
	l.DateOfBirth, l.Telephone, l.TicketsRequested, l.DateReceived, l.Email
from Contacts c
inner join (select top 1 * from LandingPage) l	
	on CreateMatchCode(l.Surname, l.PostCode, l.Address1, l.Address2, l.Address3, l.Address4, l.Address5, '') = c.MatchCode
where c.Title not like 'Mrs%'
	and c.Surname <> 'Anon'
	and l.Transferred = 1
	and l.AgeVerified = 0

It ss bringing back 0 records.

I know there are some records in both tables that match.

Be grateful for some suggestions please.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:28:26

is creatematchcode a user defined function?
if its so, the performance of join would be really a concern for large datasets

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DazlerD
Starting Member

8 Posts

Posted - 2012-01-16 : 12:31:04

Hi

Yeah CreateMatchCode uses the parameters to create a code for the contact

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:33:41

may be this?


select c.Title, c.Forename,c.Surname, c.Address1, c.Address2, 
	c.Address3, c.Address4, c.Address5, c.Town, c.Postcode,
	l.DateOfBirth, l.Telephone, l.TicketsRequested, l.DateReceived, l.Email
from Contacts c
inner join (select top 1 *,dbo.CreateMatchCode(l.Surname, l.PostCode, l.Address1, l.Address2, l.Address3, l.Address4, l.Address5, '') as MatchCode from LandingPage) l	
	on l.MatchCode = c.MatchCode
where c.Title not like 'Mrs%'
	and c.Surname <> 'Anon'
	and l.Transferred = 1
	and l.AgeVerified = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DazlerD
Starting Member

8 Posts

Posted - 2012-01-17 : 05:30:07

Sorted it thanks

Firstly I set up a temp table so I could populate a field which is the calculated MatchCode. Set up an index on this.


select ID, CreateMatchCode(Surname, PostCode, Address1, Address2, Address3, Address4, Address5, '') as MatchCode
	into dbo.#tmp_LandingPage
	from LandingPage

CREATE NONCLUSTERED INDEX IX_Control ON dbo.#tmp_LandingPage
(
    MatchCode
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Then run the code:


select c.SupporterNo, c.Title, c.Forename,c.Surname, c.Address1, c.Address2, c.Address3, c.Address4, c.Address5, c.Town, c.Postcode,
l.DateOfBirth, l.Telephone, l.TicketsRequested, l.DateReceived, l.Email

from Contacts c 
    inner join (select MAX(ID) as ID, MatchCode
            from #tmp_LandingPage
            group by MatchCode) RecentLanding on RecentLanding.MatchCode = c.MatchCode
	inner join dbo.LandingPage l on l.ID = RecentLanding.ID
where 
      c.Title not like 'Mrs%'
      and c.Surname <> 'Anon'
      and l.Transferred = 1
      and l.AgeVerified = 0
      and l.BrightsourceMailingFileID = 0

group by 
      c.SupporterNo, c.Title, c.Forename,c.Surname, c.Address1, c.Address2, c.Address3, c.Address4, c.Address5, c.Town, c.Postcode,
      l.DateOfBirth, l.Telephone, l.TicketsRequested, l.DateReceived, l.Email

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources