SQL Server 2016 join VarChar and INT to get values in Query.

Wayne Barron
Wayne Barron used Ask the Experts™
on
Hello All;

2 tables.
One of the tables (T1 in the example)
Has the ID's from T2, inserted with forward slashes 2/1/etc... separating each number.
I did it this way, so I would only have one record in the T2 table for each person's name.
And then each name's ID is referenced from the T1 WID column.

OK.
This is what my data looks like
Table = T1
Column = Values  (4 record)
WID |  1/2/5 , 2/4, 5/2, 1/4/3



Table = T2
Column = WrID
Values = 1,2,3,4,5

Open in new window


If I give it a query of
where WID=1
Then I only want to get back all records that have the number 4
So, in this case, would be the 1st and 4th record.

With the demo table(s) above.
I need to somehow join WrID and WID.
I looked at Stack Overflow, and tried the example they had, but was unable to get it to work. Or I might be missing something all together, in making it work in my case.

Any advice on this one would be great.

Wayne
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Eduard GherguArchitect - Coder - Mentor

Commented:
Hi,
JOIN is not possible in this scenario. What you can do is to add a WHERE clause condition based on a substring value like

WHERE SUBSTRING ( T1.WID, start , length )  = 4

As I can see, you have a variable number of '/'. In this case, if SUBSTRING is not an option you can use LEFT or RIGHT in order to extract the desired part. One additional possible way is to create a function that will return just the desired part of WID.

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine?view=sql-server-ver15
HuaMin ChenProblem resolver

Commented:
Hi,
You can re-structure your table to have only Int inside for the linked column. Try to change it (use Cast), to change from varchar into int.
Senior Developer
Commented:
Has the ID's from T2, inserted with forward slashes 2/1/etc... separating each number. I did it this way, so I would only have one record in the T2 table for each person's name.
Then why did you use a relational DBMS?

This is against how data models are implemented and relational engines work.. It makes the usage of indices impossible, thus you'll get the worst possible performance. It makes page compression harder, thus it may lead to more data written to disk. It requires that every consumer of your tables knows that kind of "logic" instead of using standard relational tools. It doesn't allow to use referential integrity...

And finally your description seems to be incomplete.

Maybe your looking for something like this:

DECLARE @T1 TABLE (
    WID NVARCHAR(255) NOT NULL
);

INSERT INTO @T1 ( WID )
VALUES ( '1/2/5' ) ,
       ( '2/4' ) ,
       ( '5/2' ) ,
       ( '1/4/3' );

DECLARE @T2 TABLE (
    WrID INT NOT NULL
);

INSERT INTO @T2 ( WrID )
VALUES ( 1 ) ,
       ( 2 ) ,
       ( 3 ) ,
       ( 4 ) ,
       ( 5 );

WITH Splitted
AS ( SELECT *
     FROM   @T1 T1
            CROSS APPLY STRING_SPLIT(WID, '/') SS )
SELECT *
FROM   Splitted S
       INNER JOIN @T2 T2 ON T2.WrID = S.value
WHERE  S.value = '1';

Open in new window

STRING_SPLIT() requires SQL Server 2016+, otherwise you need a custom split function. E.g. using XML split using the same table declarations:

WITH AsXml
AS ( SELECT T1.WID ,
            CAST('<r><n>' + REPLACE(T1.WID, '/', '</n><n>') + '</n></r>' AS XML) AS AsXml
     FROM   @T1 T1 ) ,
     Splitted
AS ( SELECT T1.WID ,
            R.N.value('.', 'INT') AS value
     FROM   AsXml T1
            CROSS APPLY T1.AsXml.nodes('/r/n') R(N) )
SELECT *
FROM   Splitted S
       INNER JOIN @T2 T2 ON T2.WrID = S.value
WHERE  S.value = 1;

Open in new window

Partha MandayamTechnical Director

Commented:
Please clarify what you mean by
Column = Values  (4 record)
Wayne BarronAuthor, Web Developer
Top Expert 2009

Author

Commented:
I would like to thank everyone that tried to help in this one.
I found some code I had used last year, that is just using ASP to work with the records, instead of SQL Server.
Even though using SQL Server would be preferred for speed, this does the job.

Code I am using.
theWID = left(rsInfo("WID"),len(rsInfo("WID"))-1)
theWrID = split(theWID,"/")

for each W in theWrID
" get name with the W in the Statement Query.
next

Open in new window


I would also like to explain in hopes that those who questioned about it here will have a better understanding of why I did what I did.


The below will be the records.
So, in this scenario, we will insert the name of every actor into the table, for each movie they play on.
Which gives you absolutely NO dedicated Actor ID.
T1
TID (ID)
TName (Talent Name)


--Top Gun
Tom Cruise = 1
Val Kilmer = 2
Kelly McGillis = 3

--Cocktail
Tom Cruise = 4
Elisabeth Shue = 5

--Adventures in Babysitting
Elisabeth Shue = 6

Open in new window


So, in Table Example below, we do it differently.
Giving the Talent its on table.
And then the movie will have the ID's separated by the / forward slashes
------------------
T1
TID (ID)
TName (Talent Name)

Tom Cruise = 1
Val Kilmer = 2
Kelly McGillis = 3
Elisabeth Shue = 4

T2
ID, MName, TalentID (
1, Top Gun, 	1/2/3
2, Cocktail,	1/4
3,  Adventures in Babysitting, 	4

Open in new window


I hope this will explain WHY I had to do what I did.
This actually came from an suggestion I received on EE years ago.
Is there a better way of doing it? Probably so, but this works out great for my needs.
And it does not take up a lot of space in the database doing it this way.
Compared to the First example, where every name was added for every new record created.
Partha MandayamTechnical Director
Commented:
You can have a movie table
movie_id, name
and an actor table
actor_id, name
and movie_actor table
movie_id, actor_id
which will give all the actors in a movie
This is normalized database design
Wayne BarronAuthor, Web Developer
Top Expert 2009

Author

Commented:
Thanks, Partha
I will look in on this within the coming weeks.
Wayne BarronAuthor, Web Developer
Top Expert 2009

Author

Commented:
Thanks, a lot Partha.
Usually, on things like this, I just do it without thinking.
But for some reason, on these two projects, I resorted back to something I was shown years ago, instead of using something that I have done so many times.
Your design, I was able to look back on three live sites, and I designed my databases identical to what you explained in your post.

Anyway.
I have designed my code in this soon-to-be live site, to the specs you mentioned.

Thank you, once again, for waking me up to see the true meaning of coding.

Wayne

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial