SQL query inner join error

Question

I am working on a SQL query please this one is showing this error

Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

This is the query

select 
   *,
   (select top 1 * 
    from tbl_Renewal 
    where supid = b.S_id 
      and Payed = 1 
    order by 
      renewalid desc) as result 
from 
    tbl_SuperAdmin as b 
inner join 
    tbl_Renewal as c on b.S_id = c.supid

Please check and give me the solution

Any other solution for this actual task

please find the bellow task brief detail

table 1 'tbl_SuperAdmin' having on filed

data like example

s_id name age gtc.......
1     abc  23 .........
2     cda  42 ..........

another table 2 having 'tbl_Renewal'

renewalid  renname date supid   Payed etc......
1          first   -     1        1    ........
2          first   -     2        1    ........
3          second -      1        0     ........
4          second -      1        1     ........
5          third-        1        1     ........

thanks pradeep

yogi · Accepted Answer · 2013-03-22 07:10:19Z

1

You can't put a * in single column value,

query should be something like this

select 
       *,
       (select top 1 COLUMNNAME from tbl_Renewal where supid=b.S_id and Payed=1) as result 
from tbl_SuperAdmin as b inner join tbl_Renewal as c on b.S_id=c.supid

answered Mar 22, 2013 at 7:10

yogi

19.6k13 gold badges67 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pradeep Rao Musale Over a year ago

leave it this select top 1 COLUMNNAME from tbl_Renewal where supid=b.S_id and Payed=1 give the bellow solution if putting columname also duplicates are coming

Nishad · Accepted Answer · 2013-04-24 09:12:15Z

0

cut it short to below sql

 select top 1 a.*,b.*

 from tbl_Renewal a inner join tbl_SuperAdmin as b

 on b.S_id=c.supid where a.Payed=1 order by b.renewalid desc

answered Apr 24, 2013 at 9:12

Nishad

4262 gold badges7 silver badges20 bronze badges

Collectives™ on Stack Overflow

SQL query inner join error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related