INNER JOIN with a String Challenge

Question

INNER JOIN with a String Challenge

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

bopeavy SSCertifiable Points: 5869 More actions · Answer 1

So how can I determine that based on the info you gave me? There is nothing in the #MySample table letting me know that row belongs to ZZ group.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

JohnDBA (7/15/2011)
I have to ask, Craig... REVERSE is fairly expensive performance-wise. Why any REVERSE's to begin with?
Jeff, better suggestions? Thank you!

Craig knew what I was talking about and posted a correction to his code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

JohnDBA (7/15/2011)
I have to ask, Craig... REVERSE is fairly expensive performance-wise. Why any REVERSE's to begin with?
Jeff, better suggestions? Thank you!

And, yes... I'll try to get to this on my lunch break. Your requirements (even in your first post) seem pretty clear.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nevyn SSChampion Points: 14210 More actions · Answer 4

JohnDBA (7/15/2011)
INSERT INTO #MySample VALUES (4, '
123456789 --match value 4
8943','Calan','Katin')
This record does not match because in the #MyAddress table it is NO 'A B '
This record is 'Z Z '

I think you need to think out your requirements more carefully.

The record you quoted matches TWO records in #MyAddress per your criteria (first 9 digits). One of them is an AB and the other is a ZZ

And the AB record in #MyAddress (#1) links to two separate #MySample records.

If his query is wrong, I think you've described the requirements wrong or made an error in the sample data.

Did you want each row of #MyAddress to link to only one row of #MySample? If so, how do we pick which one?

bopeavy SSCertifiable Points: 5869 More actions · Answer 5

Nevyn : Thank you for noticing.:-D

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

JohnDBA SSC Eights! Points: 812 More actions · Answer 6

So how can I determine that based on the info you gave me? There is nothing in the #MySample table letting me know that row belongs to ZZ group

That is what makes it challenging... I only what to retrieve those records that start with ‘A B ‘ and then if the nine digits match, then that record qualify for the join. Therefore, the result should only be 3 records as per this example.

I must join ID with address code where code start with 'A B ' + 9 digits.
There are 3 spaces between A and B, and there is 1 space after B.

Thank you all for your help on this!

bopeavy SSCertifiable Points: 5869 More actions · Answer 7

JohnDBA

froget the address table and look at the mysample table. You have 2 records that have that value. Once again how do you know that the record belong to ZZ...There has to be away that you know that..What is it? That is the solution to your issue.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Nevyn SSChampion Points: 14210 More actions · Answer 8

Lets try this again.

john, THIS row of #MyAddress:

INSERT INTO #MyAddress VALUES(1,'A B 123456789','123 South River','Apt 1','Miami','FL','33133')

Matches TWO rows of #MySample

INSERT INTO #MySample VALUES (1, '1234567891011','Smith','Joan')

AND

INSERT INTO #MySample VALUES (4, '1234567898943','Calan','Katin')

Because 123456789 is the first 9 characters of the "ID" for both rows of "#MySample" and that is the only joining criteria you gave us.

That is why he got 4 answers, not 3.

So what is there in the tables to tell us that the second sample record should not link to the first address? If there is nothing, you had better hope that the first 9 of ID is unique in your actual data, because the test data is broken.

Edit: and out of curiosity, how did you end up with this table design in the first place? I am assuming that either the address table links only to the customer table, that it is a many to one relationship, and that the "AB" and "ZZ" stuff denotes what type of address (home,business), or that this address table is holding the addresses for multiple other tables and that the "AB" and "ZZ" stuff is telling you which table it should link to.

bopeavy SSCertifiable Points: 5869 More actions · Answer 9

JohnDBA

The easiest thing for you to do is correct the design failure something like this:

alter table #MySample add addessCode varchar(18)

Then update it with the proper values, after that make it a foreign Key constraint.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

To redeem myself, with the A B only linked requirement, try the following:

SELECT

ms.*,

ma.*

from

#MySample AS ms

JOIN

#MyAddress AS ma

ONLEFT(ms.ID, 9) = RIGHT( ma.AddressCode, 9)

WHERE

LEFT( ma.Addresscode, 6) = 'A B '

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 11

Craig Farrell (7/15/2011)
To redeem myself, with the A B only linked requirement, try the following:
SELECT
ms.*,
ma.*
from
#MySample AS ms
JOIN
#MyAddress AS ma
ONLEFT(ms.ID, 9) = RIGHT( ma.AddressCode, 9)
WHERE
LEFT( ma.Addresscode, 6) = 'A B '

Glad you got to it for two reasons... the first reason is I had to work through my lunch break today. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)