I have 2 table:
    Table 1: tbl_account. It's content:
       account_id (varchar(10))
         4211%%
         4231%%
         42320%
         421236
         421239

    Table 2: tbl_details. It's content:
         account_id (varchar(10)) money(int)
            42113100 100
            42113200 500
            42113200 400
            42313100 600
            42313A00 780
            42314A00 220
            42123637 86
            4212394B 140
            42153486 105
            42110123 120
             ......

I want get somethings from tbl_account and tbl_details.
I have SELECT command

SELECT A.*
FROM tbl_details
WHERE left(account_id,6) IN (SELECT RTRIM(account_id) FROM tbl_account)

But, i have results is not right. I think i have prolems about with LIKE, IN....
Please, everybody show me how to solve this problem...
Thanks you very much....

Please tell us that what result you are expecting from the query.
Show sample result. Right now your query looks proper.

urt

Help yourself by helping someone.

hi tam,

-- // SQL BATCH START
declare @tbl_account table (account_id varchar(10))
declare @tbl_details table(account_id varchar(10), [money] int)

insert into @tbl_account values('4211%%')
insert into @tbl_account values('4231%%')
insert into @tbl_account values('42320%')
insert into @tbl_account values('421236')
insert into @tbl_account values('421239')

insert into @tbl_details values ('42113100', 100)
insert into @tbl_details values ('42113200', 500)
insert into @tbl_details values ('42113200', 400)
insert into @tbl_details values ('42313100', 600)
insert into @tbl_details values ('42313A00', 780)
insert into @tbl_details values ('42314A00', 220)
insert into @tbl_details values ('42123637', 86)
insert into @tbl_details values ('4212394B', 140)
insert into @tbl_details values ('42153486', 105)
insert into @tbl_details values ('42110123', 120)


SELECT *
FROM @tbl_details
WHERE left(account_id,6) IN (SELECT RTRIM(account_id) FROM @tbl_account)

-- // SQL BATCH END

OUTPUT
========
account_id money
======================
42123637 86
4212394B 140

According to your SQL statement the 6 chars of left which are matched with details table are shown.

When you want to show the data from table A and table B then using nestes SQL statement is not a right choice. you have use JOINS on these two tables and fetch those columns as you need.

so..

sql:
select a.account_id, b.money from @tbl_account a
inner join @tbl_details b on left(a.account_id, 6) = left(b.account_id, 6)

output:
account_id money
==========================
421236 86
421239 140

if you want to use "like" and "in" then

sql:
select a.account_id, b.money from @tbl_account a
inner join @tbl_details b on left(a.account_id, 4) = left(b.account_id, 4)
where b.account_id like ('%4211%')

output:
account_id money
==========================
4211 120
4211 100
4211 500
4211 400

hope, this will give you an idea. I know I am not completely answering your question.


With Regards,
Raghavendra Mudugal