Problem in sub query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Dear sir i have a problem regarding to sql query please help me

ex:

SQL

select schchargename,amount from tbschcharge where schchargeid in(select stdchrgs from tbstdadmcharges where stdadmno=4569)

schchargeid is int data type first table

stdchrgs is varchar data type in second table

this give me error cannot convert varchar to int

this qyery give result: select schchargename,amount from tbschcharge where schchargeid in(1,2,4,7)

I want this output:

Security 2500
Computer Fees 300
Building Fund 200
Tution Fees 300

Randeep Chauhan

Posted 12-May-11 2:10am

Randeep Chauhan

Updated 12-May-11 2:14am

Manfred Rudolf Bihy

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manfred Rudolf Bihy · Answer 1 · 2011-05-12T02:17:00

Solution 1

This should work:

SQL

DECLARE @list AS varchar(50);
SET @list = (SELECT stdchrgs FROM tbstdadmcharges WHERE stdadmno=4569)
SELECT schchargename,amount
FROM tbschcharge
WHERE PATINDEX(@list, LTRIM(RTRIM(CONVERT(varchar(10), schchargeid))) + ',') > 0 OR
      PATINDEX(@list, ',' + LTRIM(RTRIM(CONVERT(varchar(10), schchargeid)))) > 0

It converts the varchar in column stdchrgs to integers.

Note: This is only a workaround as integer values stored in a varchar column are an indication that something went wrong in the DB design.

Best Regards,

-MRB

Posted 12-May-11 2:17am

Manfred Rudolf Bihy

Updated 12-May-11 2:48am

v4

Comments

Randeep Chauhan 12-May-11 8:33am

Thanks for reply sir but my problem is that my value (stdchrgs) contain data like (1,2,3,4,5) so its not convergt into integer
is there any technique actualy i'm use to it for in query
like : select * from <Table> where in(stchrgs)

but it is in varchar

so, plz hlp me

Manfred Rudolf Bihy 12-May-11 8:50am

I updated my solution with something that should work for you. Please try it out.

I still yould prefer it if you would use a relational DB in the way it was meant to be used. Storing a list of integers as a varchar is not the way to do it. You should really think about remodelling your DB design.

Marc A. Brown 12-May-11 8:56am

Amen to that.

DeepikaSrivastava · Answer 2 · 2011-05-12T02:49:00

Solution 2

use below script, this should work

select schchargename,amount from tbschcharge where CAST(schchargeid as varchar) in(select stdchrgs from tbstdadmcharges where stdadmno=4569)

OR
select schchargename,amount from tbschcharge where Convert(varchar,schchargeid ) in(select stdchrgs from tbstdadmcharges where stdadmno=4569)

above i converted INT value to VARCHAR , bcoz INT can not convert to VARCHAR due to of size.

Posted 12-May-11 2:49am

DeepikaSrivastava

Updated 12-May-11 2:50am

v2

Comments

[no name] 12-May-11 9:05am

my 4

Manfred Rudolf Bihy 12-May-11 9:21am

I'm sorry to say that you solution will not work:
1. Because OP said that table 2 contains "(1,4,5,7)" as a value in column stdchrgs
2. IN will not work in the way indicated that is what PATINDEX is there for.