SQL Server Performance

subquery error

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by priyaram, Sep 12, 2008.

  1. priyaram New Member

    I created a sub query and executed..but it's not throwing up an error.
    table1: col1,col2,col3
    table2 : r1,r2,r3
    table 1 and table 2 column names are completely different.
    I created a query like
    Select col1 from table1 where col1 not in (select col1 from table2) group bycol1.
    We know that col1 is not in the table2.but the output in coming just thename col1.it’s now throwing up and error like “col1 is invalid in table2”
    I am so confused…any help will be appreciated
  2. TommCatt New Member

    For a problem like this, you need to privide the exact text of the query.
    Taking a initial stab in the dark, it sounds like you may have written the original subquery like:
    select 'col1' from table2
  3. Adriaan New Member

    If your table2 table does not have a col1 column, then your subquery is simply wrong. Why are you surprised that you get an error for that?
    In general, always provide full table names (including owner) with aliases in the FROM clause of your query, and qualify each and every reference to any column with the appropriate alias.
  4. Kobojunkie New Member

    Try thisSELECT
    col1FROM
    table1 a
    Where
    a.col1 Not in(Select b.col1 from table2)group
    by a.col1

Share This Page