Change query from sql 2000 to 2008

  • 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

  • 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]

  • 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.

  • 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]

  • 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... 🙂

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

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