asked on

Re write the below sql's Sql server 2008

Can some one please help me in re writing the below sql

select *
from grp a,inv b,fds c ,suix d, link e ,aove f
where e.id*=d.inv_id
and a.inv_id=b.inv_id
and b.fd_id=c.fd_id
and a.inv_id=d.inve_id
and a.gp_id=e.gp_id
and e.gp_id=f.gp_id
and f.gp_id=a.gp_id

There is a non -anscii join in the first where condition .Can some one help me to re write the sql so that it works in sql 2008

Thanks

Jared_S

I'm not sure what

"e.id*=d.inv_id "

is trying to do.

Do you get your desired results if you change it to

"e.id = d.inv_id"?

vijay11

ASKER

e.id*=d.inv_id means left outer join similar as

from link e
left outer join suix d on e.id=d.inv_id

ASKER CERTIFIED SOLUTION

Jared_S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vijay11

ASKER

I am getting The multi-part identifier "c.fd_id" could not be bound.

Jared_S

Ok - round 2:

select * from 

link e left outer join 

suix d on e.id = d.inv_id inner join   
 
grp a on (a.gp_id=e.gp_id and a.inv_id=d.inve_id) inner join

inv b on  a.inv_id=b.inv_id inner join

aove f on (f.gp_id=a.gp_id and f.gp_id = e.gp_id) inner join 

fds c on b.fd_id=c.fd_id

Open in new window

vijay11

ASKER

I dont get any out put.

But when I remove the 2nd condition in the below line I get some data

grp a on (a.gp_id=e.gp_id and a.inv_id=d.inve_id)

Jared_S

Are "d.inve_id" or "d.inv_id" different fields, or is that a typo in the original query?

Changing

grp a on (a.gp_id=e.gp_id and a.inv_id=d.inve_id)

to

grp a on (a.gp_id=e.gp_id and a.inv_id=d.inv_id)

might fix it if it's a typo...

vijay11

ASKER

yes it was the typo.But while running the query I fixed it and deployed it.But I get not results and when I remove the condition as stated above I get the results as the original query.

Thanks