Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Correlated subquery across databases

    I'm having a problem running a query on tables in database A, but it's being executed on database B (results need to be processed in B). When I include a correlated subquery, it doesn't seem to recognise the alias I've used. Table B holds effective dated history and I only want to return the most recent row. I'm also using an outer join which I suspect has something to do with it:

    eg.

    select a.emplid, b.effdt
    from servername.dbname.dbo.table_a
    left outer join servername.dbname.dbo.table_b
    on table_a.emplid = table_b.emplid
    where table_b.effdt = (select max(effdt)
    from table_b
    where emplid = b.emplid
    and effdt <= getdate())

    I get the following error:
    The column prefix 'B' does not match with a table name or alias used in the query. Other points:

    - If I execute the same query on database A, it works ok
    - If I take the outer join out from the above (on database B), it works ok (but I miss rows that I need)
    - If I take the outer join out, but include another condition (eg. and a.emplid = 'xxxx') I get the same error.

    Any help appreciated!

    Thanks
    Greg

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Correlated subquery across databases

    You do not set aliases:

    select a.emplid, b.effdt
    from servername.dbname.dbo.table_a -- as alias !!!!!!
    left outer join servername.dbname.dbo.table_b -- as alias !!!!!!
    on table_a.emplid = table_b.emplid
    where table_b.effdt = (select max(effdt)
    from table_b
    where emplid = b.emplid
    and effdt <= getdate())

  3. #3
    Join Date
    Sep 2003
    Posts
    2
    Sorry that was an omission from the post - even with aliases, it doesn't work as per the original message.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Could you post your query and error message?

    Originally posted by gregwilliams
    Sorry that was an omission from the post - even with aliases, it doesn't work as per the original message.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •