Change query from sql 2000 to 2008

Question

Change query from sql 2000 to 2008

smriti.subramanian

SSC Eights!

Points: 854
More actions
July 31, 2013 at 9:18 am

#280734

Hi,
I want to modify the below query which exists in 2000 to sql 2008. Can u please help me?
select *
FROM company_role_match crm,
company_role_match crm2,
company_role_match crm3,
access_control_doc acd
WHEREcrm.id=* crm3.id
ANDcrm2.id =* crm3.id
ANDcrm.user_id = 547 --@query_user_id
ANDcrm2.user_id = 4397 --@profile_id
ANDacd.ac_type = 'User Roles' --@access_control_type
ANDacd.ac_id=* crm3.id
AND (crm.granted= 1 OR crm2.granted= 1 )
ANDcrm3.id = 32 --@access_id

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Not very clear what your current query trying to do.

From one prospective, using "=*" means you want to return all data from "right" table regardless if any corresponding record is found in the "left" table, which in ANSI SQL called OUTER JOIN.

However the next filters ensure that specific records should be found in each "joined" tables, making "=*" irrelevant.

So, I guess the exact replacement for posted query would be this:

select *

FROM company_role_match crm3

join company_role_match crm on crm.id = crm3.id

join company_role_match crm2 on crm2.id = crm3.id

join access_control_doc acd on acd.ac_id = crm3.id

WHERE crm.user_id = 547 --@query_user_id

AND crm2.user_id = 4397 --@profile_id

AND acd.ac_type = 'User Roles' --@access_control_type

AND ( crm.granted = 1 OR crm2.granted = 1 )

AND crm3.id = 32 --@access_id

If not your filters for specific crm and crm2 ID's , I would rewrite it to:

select *

FROM company_role_match crm3

left join company_role_match crm on crm.id = crm3.id

left join company_role_match crm2 on crm2.id = crm3.id

left join access_control_doc acd on acd.ac_id = crm3.id AND acd.ac_type = 'User Roles'

WHERE crm3.id = @access_id

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

smriti.subramanian SSC Eights! Points: 854 More actions · Answer 2

thanks, but the query i had posted in sql 2000 returns 903 records. So i tried to add filters to ur query as below which returns 0 records.

select *

FROM company_role_match crm3

left join company_role_match crm on crm.COMPANY_ID = crm3.COMPANY_ID

left join company_role_match crm2 on crm2.COMPANY_ID = crm3.COMPANY_ID

left join access_control_doc acd on acd.ac_id = crm3.COMPANY_ID AND acd.ac_type = 'User Roles'

WHERE crm3.COMPANY_ID = 32

AND crm.user_id = 547

AND crm2.user_id = 4397

AND (crm.granted= 1 OR crm2.granted= 1 )

I want the same records to be returned by the query in 2008 also.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

smriti.subramanian (7/31/2013)
thanks, but the query i had posted in sql 2000 returns 903 records. So i tried to add filters to ur query as below which returns 0 records.
select *
FROM company_role_match crm3
left join company_role_match crm on crm.COMPANY_ID = crm3.COMPANY_ID
left join company_role_match crm2 on crm2.COMPANY_ID = crm3.COMPANY_ID
left join access_control_doc acd on acd.ac_id = crm3.COMPANY_ID AND acd.ac_type = 'User Roles'
WHERE crm3.COMPANY_ID = 32
AND crm.user_id = 547
AND crm2.user_id = 4397
AND (crm.granted= 1 OR crm2.granted= 1 )
I want the same records to be returned by the query in 2008 also.

These filters are not the same query as in your original post.

Try this:

select *

FROM company_role_match crm3

left join company_role_match crm on crm.COMPANY_ID = crm3.COMPANY_ID AND crm.user_id = 547

left join company_role_match crm2 on crm2.COMPANY_ID = crm3.COMPANY_ID AND crm2.user_id = 4397

left join access_control_doc acd on acd.ac_id = crm3.COMPANY_ID AND acd.ac_type = 'User Roles'

WHERE crm3.COMPANY_ID = 32

AND (crm.granted = 1 OR crm2.granted= 1 )

Please follow the link at the bottom of my signature. This article provides great tips about how to ask the questions on this forum in order to get most relevant and prompt answers

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

smriti.subramanian SSC Eights! Points: 854 More actions · Answer 4

Hi,

Thanks. I made little modifications to your first query and it worked.

use prdyppl

select *

FROM company_role_match crm3

left join company_role_match crm on crm.company_id = crm3.company_id and crm.user_id = 547

left join company_role_match crm2 on crm2.company_id = crm3.company_id and crm2.user_id = 4397

left join access_control_doc acd on acd.ac_id = crm3.company_id

WHERE

acd.ac_type = 'User Roles' --@access_control_type

AND ( crm.granted = 1 OR crm2.granted = 1 )

AND crm3.company_id = 32 --@access_id

Thanks for showing the right path... 🙂