Left Join INT on Varchar

Question

Im writing a report to show what features our clients want when building there home. I want to left join 2 tables however the way the data is stored its making it difficult for me to do the join.

Table 1 tbl_Main_Holding has a field called Requirements and the data is stored as a varchar and can have multiple values like 1,4,7

1 = "Eco-Build"
4 = "Conservatory"
7 = "Basement"

Table 2 [tbl_Features] has the fields ID (INT) and Description (Varchar)

SELECT * FROM dbo.tbl_Main_Holding AS rm 
LEFT JOIN [dbo].[tbl_Features] AS f
ON rm.Requirements = f.id

The join below wont work as i would need to convert the varchar to INT However that's not my problem my problem is how do i show the results of clients that have selected multiple feature, how dopes this left join work? Im using SQL Server 2008 and the data for both tables are store as so.

So in the Requirements field the data can actually have comma delimited values? — SS_DBA, Oct 31 '16 at 12:44
Yes the data is stored as a varchar and looks like "1,4,7" Seperated by Commas — RustyHamster, Oct 31 '16 at 12:47
Please provide the DDL for your table and/or sample data for both tables. — S3S, Oct 31 '16 at 12:51
Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutly yes!** — Zohar Peled, Oct 31 '16 at 12:52
Try searching for questions on [splitting strings](http://stackoverflow.com/questions/10914576/t-sql-split-string). In an ideal world, I'd recommend adding a [cross reference table](http://stackoverflow.com/questions/321127/how-do-i-create-a-cross-reference-table-query-for-my-data), to bridge the gap between holdings and requirements. Of course, I appreciate you may not be able to update the db design. — David Rushton, Oct 31 '16 at 12:53
I think, You should write a split function for your further development .. — vipin, Oct 31 '16 at 12:54
Possible duplicate of [Joining a table based on comma separated values](http://stackoverflow.com/questions/26236436/joining-a-table-based-on-comma-separated-values) — Tab Alleman, Oct 31 '16 at 12:56

score 5 · Accepted Answer · edited May 23 '17 at 11:33

Step 1 is to go and find the person that designed this table structure (even if it is you) then whack them round the head with a stick.

Step 2 is to redesign the tables, a junction table is what is required here, not stuffing multiple integers into a single varchar column. For good measure at the end of step two you should hit the original designer with a stick again.

CREATE TABLE tbl_Main_Holding_Requirements
(
    MainHoldingID INT NOT NULL, --FK TO `tbl_main_Holding`
    FeatureID INT NOT NULL -- FK TO Require `tbl_Features`
);

Now, each requirement represents a row in this table, rather than a new item on your list, so your join is now simple:

SELECT  * 
FROM    dbo.tbl_Main_Holding AS rm 
        LEFT JOIN dbo.tbl_Main_Holding_Requirements AS r
            ON r.MainHoldingID = rm.ID
        LEFT JOIN [dbo].[tbl_Features] AS f
            ON f.ID = r.FeatureID;

If you need to bring this back up to a comma delimited list, then you can do it in the presentation layer, or with SQL-Server's XML Extensions:

SELECT  *,
        Features = STUFF(f.Features.value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    dbo.tbl_Main_Holding AS rm 
        OUTER APPLY
        (   SELECT  CONCAT(',', f.Description)
            FROM    dbo.tbl_Main_Holding_Requirements AS r
                    INNER JOIN [dbo].[tbl_Features] AS f
                        ON f.ID = r.FeatureID
            WHERE   r.MainHoldingID = rm.ID
            FOR XML PATH(''), TYPE
        ) f (Features);

If step two is not possible, then you can get around this using LIKE:

SELECT  * 
FROM    dbo.tbl_Main_Holding AS rm 
        LEFT JOIN [dbo].[tbl_Features] AS f
            ON ',' + rm.Requirements + ',' LIKE '%,' + CONVERT(VARCHAR(10), f.ID) + ',%';

Once again, if the features need to be reduced back to a single row, then you can use XML extensions again:

SELECT  *,
        Features = STUFF(f.Features.value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    dbo.tbl_Main_Holding AS rm 
        OUTER APPLY
        (   SELECT  CONCAT(',', f.Description)
            FROM    [dbo].[tbl_Features] AS f
            WHERE   ',' + rm.Requirements + ',' LIKE '%,' + CONVERT(VARCHAR(10), f.ID) + ',%'
            FOR XML PATH(''), TYPE
        ) f (Features);

Another option is to split the comma separated values into a list using some kind of Split function, but as the testing in this article shows, if you don't need to access the individual values from the list, it is more efficient to just use LIKE.

Haha unfortunately Im not the database architecture however i know where he is so will follow up with step one after i get this report working. — RustyHamster, Oct 31 '16 at 13:07
Joking aside (I wouldn't actually suggest hitting people with sticks as an effective teaching method), it really would be better to fix this at source, rather than having to work around it for ever more. The longer your dba leaves it before fixing it, the more reports/views/procedures etc will reference the table and data, and the longer the fix will take, and the more errors it will cause. — GarethD, Oct 31 '16 at 13:18
Totally agree with you. Unfortunately the need for reporting on the data we have was never looked at as a need while they built the system. Now when reports are required Im having a nightmare to show the data correctly. — RustyHamster, Oct 31 '16 at 13:24
I have opted to go with your XML solution will work better for reporting reasons, Thanks GarethD — RustyHamster, Oct 31 '16 at 13:26

score 1 · Answer 2 · edited May 23 '17 at 12:26

1

As I wrote in my comment, please read Is storing a delimited list in a database column really that bad?
You really should normalize your database to avoid these things.

Now, assuming you can't change the database schema, there is a simple trick with like that you can use:

SELECT * FROM dbo.tbl_Main_Holding AS rm 
LEFT JOIN [dbo].[tbl_Features] AS f
ON ',' + rm.Requirements +',' LIKE '%,' + CAST(f.id as varchar(10)) + ',%'

Note that I've added a comma before and after the rm.Requirements column and also before and after the f.id column.

edited May 23 '17 at 12:26

Community

1
1

answered Oct 31 '16 at 12:57

Zohar Peled

79,642
10
69
121

Hi Zohar thanks for the suggestion however this wont work as it only brings back the first match. For instance if the client and said there requirments are 1,4,6 then it only brings back one record =1 "Eco Build" It doesnt show the other features they have requested. – RustyHamster Oct 31 '16 at 13:15
It should return a record for each feafure - so you should have 3 records in your requirements are 1,4 and 6. – Zohar Peled Oct 31 '16 at 13:39
hI Zohar Im only get one record back and is matching it with the first entry – RustyHamster Oct 31 '16 at 14:50
I must be missing something then. Can't test right now, will try to make a demo tomorrow. – Zohar Peled Oct 31 '16 at 17:32

Left Join INT on Varchar

2 Answers2