Trouble with a 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)

Trouble with a Join

Author

Topic

jrlanders
Starting Member

15 Posts

Posted - 2004-06-24 : 22:16:37

Hi,
I am having difficulty figuring out how to do a join on a table where the data value in the field is seperated by a ;
Ex: 1;3

Here is the statement:


		Set NOCOUNT on
		Select * From partner Inner Join
			coverage on partner_covr_ID = cover_ID
		Inner Join partner_status on partner_status = partnstat_ID
		Inner Join state on partner_st = state_ID
		Inner Join PARTNER_TARGET on partner_target like partn_targ_id
		Where partner_region_ID = @Region and partner_life = 1 
		Order by partner_covr_ID, partner_target, partner_status

The joins that work are:


		Inner Join partner_status on partner_status = partnstat_ID
		Inner Join state on partner_st = state_ID

The one I have trouble with is:


		Inner Join PARTNER_TARGET on partner_target like partn_targ_id

(chr)partner_target = "1;3"
(int)partn_targ_id = 1

The field partner_target can have several Data items in it "1;3;5;9". I want to match the join if partn_targ_id matches any one of the numbers in partner_target. Any one have an Idea how to do that inside the SQL statement? Oh... the is a stored procedure.

Thank you in advance

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-24 : 22:25:16

ON partner_target LIKE '%' + CAST(partn_targ_id AS VARCHAR) + '%'

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-24 : 23:03:21

ON ';' + partner_target + ';' LIKE '%;' + CAST(partn_targ_id AS VARCHAR) + ';%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jrlanders
Starting Member

15 Posts

Posted - 2004-06-24 : 23:15:04

Excellent... This is very close Sam.
It found the partn_targ_id that equils '1'

I then load the description for that record that it found... It also needs to lookup the 3 in the "1;3" and find that description as well.

Can that be done in the same statement?

Thanks for the prompt response.

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-25 : 08:27:41

quote:
Originally posted by jrlanders

Excellent... This is very close Sam.
It also needs to lookup the 3 in the "1;3" and find that description as well.

Can that be done in the same statement?

Nigel's modification should do it.

jrlanders
Starting Member

15 Posts

Posted - 2004-06-25 : 10:36:45

His nodification was great... Thank you Nigel. Only it does not quite accomplish what I am after. Let me see if I can clarify.

The Data field: (chr)partner_target = "1;3" Holds two values that I need to look up and translate that value to a description of that value held in another table, the table PARTNER_TARGET

The join:


Inner Join PARTNER_TARGET ON ';' + partner_target + ';' LIKE '%;' + CAST(partn_targ_id AS VARCHAR) + ';%'

is successfull in finding the first match, the "1". I then grab the description that goes with that "1"
I also need to lookup the "3" and grab that descriptions as well.

Does that make sense? I don't see the suggestion that Nigel make grabbing both descriptions. Could be I did something wrong.

Let me know you thoughts.

Thanks for all the help guys.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-25 : 13:01:27

for 1;3 the join will give 2 rows from PARTNER_TARGET, one for 1 and one for 3.
so for
select partner_target, partn_targ_id, ...
from partner
Inner Join PARTNER_TARGET
ON ';' + partner_target + ';' LIKE '%;' + CAST(partn_targ_id AS VARCHAR) + ';%'
where partner_target = '1;3'

you should get

1;3, 1, ...
1;3, 3, ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-06-25 : 13:24:49

and, of course, the most important thing we've learned from all this: DON'T store the data in 1 column seperated by ";" ! store it in a related table, that's what relational databases are designed for.

Mark Caldwell had a great blog about this maybe 6 months ago; check out weblogs.sqlteam.com.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources