Join Question

Is it possible to do something like the following:

Select t1.Branch, t2.SCNamefrom WWFE07BoothLeadsAs t1JOIN New_Products.dbo.SupportCentersAs t2ON t1.BranchLike't2.SCName%'

We cannot join the tables like that. If you provide your requirement we can provide the query for that.

|||

Not a clean solution at all, but at least works, if you need it urgently:

Select t1.Branch, t2.SCName
from WWFE07BoothLeads As t1,
New_Products.dbo.SupportCenters As t2
where SUBSTRING(t2.SCName, 1, len(t1.Branch))=t1.Branch

If somebody knows a nicer way of doing it, please share :)

|||

Select t1.Branch, t2.SCName
from WWFE07BoothLeads As t1,
New_Products.dbo.SupportCenters As t2
where SUBSTRING(t2.SCName, 1, len(t1.Branch))=t1.Branch

Needs to be:
SELECT

t1.Branch,

t2.SCName

FROM WWFE07BoothLeads t1

INNER JOIN New_Products t2 ON t2.t1ID = t1.ID

WHERE t1.ID = @.Id //Or whatever condition you want

Make sense?

|||

WoW!! That works great!! There are only 605 original records but the query produces 608. Not sure why that is.

What I am trying to accomplish is to update the original table (WWFE07BoothLeads ) with a branchId

Select t1.Branch, t2.SCName, t2.SCID
from WWFE07BoothLeads As t1,
New_Products.dbo.SupportCenters As t2
where SUBSTRING(t2.SCName, 1, len(t1.Branch))=t1.Branch

Update WWFE07BoothLeads Set BoothId = ?(1) Where Branch = ?(Atlanta)

|||

Prob a data/query issue. Post your query.

|||

Select t1.Branch, t2.SCNamefrom WWFE07BoothLeadsAs t1
JOIN New_Products.dbo.SupportCentersAs t2
ON t1.BranchLiket2.SCName+'%'

|||

Ok... so my original idea was correct.

Select t1.Branch, t2.SCName, SCID from WWFE07BoothLeads As t1
JOIN New_Products.dbo.SupportCenters As t2
ON t2.SCName Like t1.Branch+'%'

I had to modify it a little bit..

|||

Yes your original query is correct except 't1.Branch%' except specify the value in the completely single quote which is corrected now.

There is scope of getting more records than the actual join as you already mentioned in your message.