SQL Subquery and CAST not working

Question

I am trying to get data from one table which is in varchar form and pass the same to other query which has same field as int.

Table Article with following fields

(ArticleID int, ArticleTitle nvarchar(200), ArticleDesc nvarchar(MAX))

Sample data

1 Title1 Desc1
2 Title2 Desc2
3 Title3 Desc3

I have another Table called Banner which has banner related to Articles

(BannerID int, BannerName nvarchar(200), BannerPath nvarchar(MAX), ArticleID varchar(200))

Sample Data

**BannerID BannerName BannerFile ArticleID**
    100 Banner1 BannerPath1 '1','3','5'
    101 Banner2 BannerPath2 '2','3','5'
    102 Banner3 BannerPath3 '8','3','5'
    103 Banner4 BannerPath4 '10','30','5','2','3','5'

Sample Query

SELECT ArticleTitle 
FROM Article 
WHERE CAST(ArticleID AS varchar(200)) IN (
    SELECT  ArticleID FROM Banner WHERE BannerID = 2
)

In my actual project i have multiple fields in banner table so that i can assign banner article, writer, Category, Pages.. For this reason i decided to store ArticleID or WriteID or CatID as single field in this format'10','30','5','2','3','5'

I change my structure then i may end up create hundreds of records for one banner and one banner can be assigned to any one of this article, writer, Category, Pages

Query below return zero rows may be my casting is creating problem I would appreciate how i can get arounds this without changing my database structure

SELECT ArticleTitle FROM Article WHERE CAST(ArticleID AS varchar(200)) IN (SELECT  ArticleID FROM Banner WHERE BannerID = 2)

UPDATED:

No offense to any one i have decided to stick to my design as the question i had asked was for backed reporting section of the website which won't be used to often. I can be wrong regarding not normalizing the tables ...

My actual scenario Suppose users visit url

`abc.com/article/article.aspx?articleID=30&CatID=10&PageID=3&writerID=3`

Based on this url i can run four queries with UNION to get the required banner off-course i have to decide on banner precedence so i will do it like this

     `SELECT  BannerName, BannerImage FROM Banner WHERE ArticleID LIKE '%''30''%'`
    UNION ALL
    `SELECT  BannerName, BannerImage FROM Banner WHERE CategoryID LIKE '%''10''%'`
     UNION ALL
     `Another query .......`

If i do it this way then query will have to look for banners in single table with few rows But if i normalize table based on JW which is good way of doing it may result in 30-40 rows for each banner in different table which may effect performance as i have to add new banner for new articles (for new magazine issues).

I know i am breaking every law of normalizing but i am afraid i have to do it for performance as i may end up having 2000 rows for every 100 banners & this will grow with time.

Updated Again

I hope this image will give you an over view of what i am trying to do

If i do it this way then i only need 1 row per banner & if i further normalize and create more table then i might end up having several row for one banner for example Taking above image sample from banner table then my first banner will have 27 Rows Second Banner 11 Rows Thirds Banner 14 rows.

In order to avoid this i thought of to store multiple articleID, IssueID, PageID .... in their respected fields. This approach might be dirty but it is working.

I Definitely had some -ve feedback which from their point of view is understandable.. Since i have provided further details is my approach totally unprofessional or it is fine keeping in mind that website might have very good traffic & this approach may be faster.

Answer 1

Fully agree the above example is bad and not the correct way for what he is doing. However the root error message issue still exist and is a problem under some conditions.

My situation is working with a table to hold some custom form field element data. Without laying out the entire structure I’ll just lay out what is needed to show and reproduce the issue. I can also confirm the issue resolves around the IsNumeric in this case. Combined with the SubQueries as well. The sample holds two items, item name simulating the custom field element/type and the field value. Some are names, and some are minutes of labor. Could be weights, temps, distances, whatever, it’s customer definable extra data.

Create Table dboSample (cKey VarChar(20), cData VarChar(50))

Insert Into dboSample (cKey, cData) Values ('name', 'Jim')
Insert Into dboSample (cKey, cData) Values ('name', 'Bob')
Insert Into dboSample (cKey, cData) Values ('labortime', '60')
Insert Into dboSample (cKey, cData) Values ('labortime', '00')
Insert Into dboSample (cKey, cData) Values ('labortime', '15')

Select * From (Select * From dboSample Where IsNumeric(cData) = 1) As dboSampleSub Where Cast(cData As Int) > 0

Resulting in an error “Conversion failed when converting the varchar value 'Jim' to data type int.”

The lower nested query has a where clause limiting returned rows to only included numeric based data. However the cast in the higher level is clearly seeing rows not included in the sub query return. It is in fact seeing and processing data of the lower nested query. Cannot locate an Select OPTION flags to prevent this.

Answer 2

In additio to John Woo's excellent answer, I will try to answer the question "Why doesn't the query return any results".

I'm going to leave aside the WHERE b.BannerID = 2 clause, which is obviously not met by any of the sample records.

The main issue with the query is the IN clause. IN will tell you whether an item is found in a set of items. What you are expecting it to do is iterate through a set of sets and tell you whether the item is found.

To illustrate this, here are two simplified queries:

-- this will print 0
if '1' in ('''1'',''3'',''5''')
    print 1
else 
    print 0

-- this will print 1
if '1' in ('1', '3', '5')
    print 1
else 
    print 0

The main point is that IN is a set-based operation, not a string function that will find a substring.

One possible solution to your problem would be to use CHARINDEX to perform the substring detection:

select ArticleTitle 
from Article a
join Banner b
  on charindex(CAST(a.ArticleID AS varchar(200)), b.ArticleID) > 0

This version is incorrect, because searching for the id '1' will also match values like '11','12'.

In order to get correct results, you could end up with a query similar to this (in order to make sure you only match on values between asterisks):

select ArticleTitle 
from Article a
join Banner b
  on charindex('''' + CAST(a.ArticleID AS varchar(200)) + '''', b.ArticleID) > 0

SQLFiddle: http://www.sqlfiddle.com/#!3/2ee3c/23

This query, however, has two big disadvantages:

• it gets awfully slow for relatively big tables, as it cannot use any indexes and needs to scan the Banner table for each row in Article
• the code got a little bit more complex and the more functionality you'll add to it, the harder it will get to reason about it, resulting in maintainability problems.

These two problems are smells that you are doing something wrong. Following JW's solution will get rid of the two problems.

Answer 3

It is a very bad design when you have saved comma separated values in a column when these values will be used in searching of records.

You need to properly normalize and restructure the table into 3-table design because I can see a Many-to-Many relationship on Article and Banner.

Suggested Schema design:

Article Table

• ArticleID (PK)
• ArticleTitle
• ArticleDesc

Banner Table

• BannerID (PK)
• BannerName
• BannerPath

Article_Banner Table

• ArticleID (FK) (Also a compound PK with BannerID)
• BannerID (FK)

and by this design you can simply query your records like:

SELECT  a.*
FROM    Article a
        INNER JOIN Article_Banner b
            ON a.ArticleID = b.ArticleID
WHERE   b.BannerID = 2

advantages of the structure:

• can easily create query statements
• can take advantage of the indexes defined
• etc..