SQL 2008

I tried to use a where statment on my code but its not working

select * from Tablename
Where  Order.OrderNo like (Invoice.OrderNo +'%')
Example:
Order.OrderNo = 1000
Invoice.OrderNo = 1000C
Is there another way to do that?

Thanks
DBA2010Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
try this.
select * 
  from [Order] o
  join [Invoice] i 
    on o.OrderNo like i.OrderNo+'%'

Open in new window

or
select * 
  from [Order] o
 where exists (select 1 from [Invoice] i where o.OrderNo like i.OrderNo+'%')

Open in new window

0
 
Ephraim WangoyaCommented:

you need to join the tables

select *
from Order
inner join invoice on charindex(Order.orderNo, Invoice.OrderNo, 1) > 0

0
 
Ephraim WangoyaCommented:
cast as order.orderno as varchar

select *
from Order
inner join invoice on charindex(cast(Order.orderNo as varchar), Invoice.OrderNo, 1) > 0
0
 
DBA2010Author Commented:
why is that?
0
 
Ephraim WangoyaCommented:

To compare fields from two tables, they need to be linked in some way, in this case I'm using a join
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.