Problem with SQL script

still a newb at this, so i would appreciate a little help.
I keep getting an error of : The column prefix 'bur' does not match with a
table name or alias name used in the query
I am querying across 2 databases. "bur" is an alias (set in the same way as
the other aliases) , but it does not seem to be recognised.
The "MYA_BUS_UNIT_RISK_UWY" and "MYA_GROUP_MEMBER" are in the ARM-Live
database. I am using SQL Query Analyser, with the default database set as the
Arms_Live database.

select
ctb.company_no Entity,
ctb.no Branch_Number,
ctbu.name Branch_Name,
GMAE.GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE
from
MYA_BUS_UNIT_RISK_UWY bur,
MYA_GROUP_MEMBER
LEFT JOIN MYA_GROUP_MEMBER GMC1 ON (
GMC1.GRP_GROUP_ID = bur.GRP_CLIENT_GROUP_ID AND
GMC1.GM_GROUP_MEMBER_ID = bur.GM_CLIENT_MEMBER_ID
) LEFT JOIN MYA_GROUP_MEMBER GMB1 ON (
GMB1.GRP_GROUP_ID = bur.GRP_BUS_UNIT_GROUP_ID AND
GMB1.GM_GROUP_MEMBER_ID = bur.GM_BUS_UNIT_MEMBER_ID
) LEFT OUTER JOIN MYA_GM_RELATED_TO_GROUP_MEMBER RGM ON (
RGM.GRP_SECOND_GROUP_ID = GMC1.GRP_GROUP_ID AND
RGM.GM_SECOND_GROUP_MEMBER_ID = GMC1.GM_GROUP_MEMBER_ID AND
RGM.GMR_GROUP_MEMBER_RELATIONSHIP = 10
) LEFT JOIN MYA_GROUP_MEMBER GMAE ON (
GMAE.GRP_GROUP_ID = RGM.GRP_FIRST_GROUP_ID AND
GMAE.GM_GROUP_MEMBER_ID = RGM.GM_FIRST_GROUP_MEMBER_ID
),
Ability_Live.dbo.ct_branch ctb,
Ability_Live.dbo.ct_business ctbu
where ctb.business_no = ctbu.no

"SeanL" wrote in message
news:F7DBB840-06BE-4237-A44F-A802FB3C238A-at-microsoft.com...
> still a newb at this, so i would appreciate a little help.
> I keep getting an error of : The column prefix 'bur' does not match with a
> table name or alias name used in the query
> I am querying across 2 databases. "bur" is an alias (set in the same way
> as
> the other aliases) , but it does not seem to be recognised.
> The "MYA_BUS_UNIT_RISK_UWY" and "MYA_GROUP_MEMBER" are in the ARM-Live
> database. I am using SQL Query Analyser, with the default database set as
> the
> Arms_Live database.
>
> select
> ctb.company_no Entity,
> ctb.no Branch_Number,
> ctbu.name Branch_Name,
> GMAE.GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE
> from
> MYA_BUS_UNIT_RISK_UWY bur,
> MYA_GROUP_MEMBER
> LEFT JOIN MYA_GROUP_MEMBER GMC1 ON (
> GMC1.GRP_GROUP_ID = bur.GRP_CLIENT_GROUP_ID AND
> GMC1.GM_GROUP_MEMBER_ID = bur.GM_CLIENT_MEMBER_ID
> ) LEFT JOIN MYA_GROUP_MEMBER GMB1 ON (
> GMB1.GRP_GROUP_ID = bur.GRP_BUS_UNIT_GROUP_ID AND
> GMB1.GM_GROUP_MEMBER_ID = bur.GM_BUS_UNIT_MEMBER_ID
> ) LEFT OUTER JOIN MYA_GM_RELATED_TO_GROUP_MEMBER RGM ON (
> RGM.GRP_SECOND_GROUP_ID = GMC1.GRP_GROUP_ID AND
> RGM.GM_SECOND_GROUP_MEMBER_ID = GMC1.GM_GROUP_MEMBER_ID AND
> RGM.GMR_GROUP_MEMBER_RELATIONSHIP = 10
> ) LEFT JOIN MYA_GROUP_MEMBER GMAE ON (
> GMAE.GRP_GROUP_ID = RGM.GRP_FIRST_GROUP_ID AND
> GMAE.GM_GROUP_MEMBER_ID = RGM.GM_FIRST_GROUP_MEMBER_ID
> ),
> Ability_Live.dbo.ct_branch ctb,
> Ability_Live.dbo.ct_business ctbu
> where ctb.business_no = ctbu.no
>

Hi

GMAE.GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE
If the columns is GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE then use

GMAE.[GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE]
but I suspect it is two different columns from two different tables


from
MYA_BUS_UNIT_RISK_UWY bur,
MYA_GROUP_MEMBER


MYA_GROUP_MEMBER seems to be unnecessary as this is not joined in any way.

Ability_Live.dbo.ct_branch ctb,
Ability_Live.dbo.ct_business ctbu

This table is not in the from clause and if these are columns they should
not be where they are.


You may want to post the DDL for the tables and example data (as insert
statements) and what you are trying to achieve.

John

"John Bell" wrote:

>
> "SeanL" wrote in message
> news:F7DBB840-06BE-4237-A44F-A802FB3C238A-at-microsoft.com...
> > still a newb at this, so i would appreciate a little help.
> > I keep getting an error of : The column prefix 'bur' does not match with a
> > table name or alias name used in the query
> > I am querying across 2 databases. "bur" is an alias (set in the same way
> > as
> > the other aliases) , but it does not seem to be recognised.
> > The "MYA_BUS_UNIT_RISK_UWY" and "MYA_GROUP_MEMBER" are in the ARM-Live
> > database. I am using SQL Query Analyser, with the default database set as
> > the
> > Arms_Live database.
> >
> > select
> > ctb.company_no Entity,
> > ctb.no Branch_Number,
> > ctbu.name Branch_Name,
> > GMAE.GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE
> > from
> > MYA_BUS_UNIT_RISK_UWY bur,
> > MYA_GROUP_MEMBER
> > LEFT JOIN MYA_GROUP_MEMBER GMC1 ON (
> > GMC1.GRP_GROUP_ID = bur.GRP_CLIENT_GROUP_ID AND
> > GMC1.GM_GROUP_MEMBER_ID = bur.GM_CLIENT_MEMBER_ID
> > ) LEFT JOIN MYA_GROUP_MEMBER GMB1 ON (
> > GMB1.GRP_GROUP_ID = bur.GRP_BUS_UNIT_GROUP_ID AND
> > GMB1.GM_GROUP_MEMBER_ID = bur.GM_BUS_UNIT_MEMBER_ID
> > ) LEFT OUTER JOIN MYA_GM_RELATED_TO_GROUP_MEMBER RGM ON (
> > RGM.GRP_SECOND_GROUP_ID = GMC1.GRP_GROUP_ID AND
> > RGM.GM_SECOND_GROUP_MEMBER_ID = GMC1.GM_GROUP_MEMBER_ID AND
> > RGM.GMR_GROUP_MEMBER_RELATIONSHIP = 10
> > ) LEFT JOIN MYA_GROUP_MEMBER GMAE ON (
> > GMAE.GRP_GROUP_ID = RGM.GRP_FIRST_GROUP_ID AND
> > GMAE.GM_GROUP_MEMBER_ID = RGM.GM_FIRST_GROUP_MEMBER_ID
> > ),
> > Ability_Live.dbo.ct_branch ctb,
> > Ability_Live.dbo.ct_business ctbu
> > where ctb.business_no = ctbu.no
> >
>
> Hi
>
> GMAE.GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE
> If the columns is GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE then use
>
> GMAE.[GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE]
> but I suspect it is two different columns from two different tables
>
>
> from
> MYA_BUS_UNIT_RISK_UWY bur,
> MYA_GROUP_MEMBER
>
>
> MYA_GROUP_MEMBER seems to be unnecessary as this is not joined in any way.
>
> Ability_Live.dbo.ct_branch ctb,
> Ability_Live.dbo.ct_business ctbu
>
> This table is not in the from clause and if these are columns they should
> not be where they are.
>
>
> You may want to post the DDL for the tables and example data (as insert
> statements) and what you are trying to achieve.
>
> John

Your comment :"GMAE.[GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE]
but I suspect it is two different columns from two different tables"
is way off - GM_GROUP_MEMBER_NAME is the column and ACCOUNT_EXECUTIVE is the
heading that the result will fall under.

your comment: "MYA_GROUP_MEMBER seems to be unnecessary as this is not
joined in any way"
is also incorrect. It is the start of the join that you are commenting on.

Your comment on: "Ability_Live.dbo.ct_branch ctb,
> Ability_Live.dbo.ct_business ctbu > This table is not in the from clause and if these are columns they should not be where they are."
is also incorrect. They are in the from clause, line 24 and 25, just before
the where clause.

No offence, but it would seem that either i am completely lost\hopeless, or
you are not interpretting the query correctly

Sean

The problem is that you are mixing the old join style and the new join
style:

> from
> MYA_BUS_UNIT_RISK_UWY bur,
> MYA_GROUP_MEMBER
> LEFT JOIN MYA_GROUP_MEMBER GMC1 ON (

That should be:

FROM MYA_BUS_UNIT_RISK_UWY AS bur
LEFT JOIN MYA_GROUP_MEMBER AS GMC1 ON (


--
Plamen Ratchev
http://www.SQLStudio.com

Plamen,

Your a superstar, thankyou.

"SeanL" wrote in message
news:32F0E8A8-38A5-45B5-899A-37EB8D31CD27-at-microsoft.com...
>
>
> "John Bell" wrote:
>
>>
>> "SeanL" wrote in message
>> news:F7DBB840-06BE-4237-A44F-A802FB3C238A-at-microsoft.com...
>> > still a newb at this, so i would appreciate a little help.
>> > I keep getting an error of : The column prefix 'bur' does not match
>> > with a
>> > table name or alias name used in the query
>> > I am querying across 2 databases. "bur" is an alias (set in the same
>> > way
>> > as
>> > the other aliases) , but it does not seem to be recognised.
>> > The "MYA_BUS_UNIT_RISK_UWY" and "MYA_GROUP_MEMBER" are in the ARM-Live
>> > database. I am using SQL Query Analyser, with the default database set
>> > as
>> > the
>> > Arms_Live database.
>> >
>> > select
>> > ctb.company_no Entity,
>> > ctb.no Branch_Number,
>> > ctbu.name Branch_Name,
>> > GMAE.GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE
>> > from
>> > MYA_BUS_UNIT_RISK_UWY bur,
>> > MYA_GROUP_MEMBER
>> > LEFT JOIN MYA_GROUP_MEMBER GMC1 ON (
>> > GMC1.GRP_GROUP_ID = bur.GRP_CLIENT_GROUP_ID AND
>> > GMC1.GM_GROUP_MEMBER_ID = bur.GM_CLIENT_MEMBER_ID
>> > ) LEFT JOIN MYA_GROUP_MEMBER GMB1 ON (
>> > GMB1.GRP_GROUP_ID = bur.GRP_BUS_UNIT_GROUP_ID AND
>> > GMB1.GM_GROUP_MEMBER_ID = bur.GM_BUS_UNIT_MEMBER_ID
>> > ) LEFT OUTER JOIN MYA_GM_RELATED_TO_GROUP_MEMBER RGM ON (
>> > RGM.GRP_SECOND_GROUP_ID = GMC1.GRP_GROUP_ID AND
>> > RGM.GM_SECOND_GROUP_MEMBER_ID = GMC1.GM_GROUP_MEMBER_ID AND
>> > RGM.GMR_GROUP_MEMBER_RELATIONSHIP = 10
>> > ) LEFT JOIN MYA_GROUP_MEMBER GMAE ON (
>> > GMAE.GRP_GROUP_ID = RGM.GRP_FIRST_GROUP_ID AND
>> > GMAE.GM_GROUP_MEMBER_ID = RGM.GM_FIRST_GROUP_MEMBER_ID
>> > ),
>> > Ability_Live.dbo.ct_branch ctb,
>> > Ability_Live.dbo.ct_business ctbu
>> > where ctb.business_no = ctbu.no
>> >
>>
>> Hi
>>
>> GMAE.GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE
>> If the columns is GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE then use
>>
>> GMAE.[GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE]
>> but I suspect it is two different columns from two different tables
>>
>>
>> from
>> MYA_BUS_UNIT_RISK_UWY bur,
>> MYA_GROUP_MEMBER
>>
>>
>> MYA_GROUP_MEMBER seems to be unnecessary as this is not joined in any
>> way.
>>
>> Ability_Live.dbo.ct_branch ctb,
>> Ability_Live.dbo.ct_business ctbu
>>
>> This table is not in the from clause and if these are columns they should
>> not be where they are.
>>
>>
>> You may want to post the DDL for the tables and example data (as insert
>> statements) and what you are trying to achieve.
>>
>> John
>
> Your comment :"GMAE.[GM_GROUP_MEMBER_NAME ACCOUNT_EXECUTIVE]
> but I suspect it is two different columns from two different tables"
> is way off - GM_GROUP_MEMBER_NAME is the column and ACCOUNT_EXECUTIVE is
> the
> heading that the result will fall under.
>
> your comment: "MYA_GROUP_MEMBER seems to be unnecessary as this is not
> joined in any way"
> is also incorrect. It is the start of the join that you are commenting on.
>
> Your comment on: "Ability_Live.dbo.ct_branch ctb,
>> Ability_Live.dbo.ct_business ctbu > This table is not in the from clause
>> and if these are columns they should not be where they are."
> is also incorrect. They are in the from clause, line 24 and 25, just
> before
> the where clause.
>
> No offence, but it would seem that either i am completely lost\hopeless,
> or
> you are not interpretting the query correctly
>
> Sean

If you formatted the query to be readable, when posted it would have helped,
and you may have spotted the issue yourself. Readabiliy is one of the keys
to maintainability.

John