SQL Query Single table with multiple results for same id

DECLARE @ItemToExclude AS VARCHAR(10)
SET @ItemToExclude = 'Item2'

SELECT A.id, A.Company,A.Item
FROM <table> A
WHERE Item <> @ItemToExclude -- Find IDs that Do Not Have the ItemToExclude
AND id NOT IN --AND That Do Not have entries with the Item to Exclude
(
SELECT id
FROM <table> X
WHERE Item = @ItemToExclude
)

something like this might be what you want:

with singleIDs as (
select id, count(*) records
from theTable
group by id
having count(*) = 1)

select yourFields
from theTable join singleIDs on theTable.Id = singleIDs.id

Something like this kind of query will get you the expected output.

SELECT A.Id,A.CompanyId, A.Item
FROM CompanyTable A
INNER JOIN CompanyTable A1 ON A.ID = A1.ID AND A1.Item <> 'Item1'
INNER JOIN CompanyTable A2 ON A.ID = A2.ID AND A2.Item = 'Item2'

SELECT  *
FROM    [Table] t1
WHERE   NOT EXISTS ( SELECT 1
                     FROM   [Table] t2
                     WHERE  t1.ID = t2.ID
                            AND t2.Item = 'Item2' )

You might want to use and in the where clause so the query will be like

SELECT * FROM table WHERE ID='789123' and Item='Item1'

You are looking for a Left Excluding JOIN of your table and (your table with all 'Item2') rows:

With this input:

create table t (id int, company char(10), item char(10));
insert into t (id,company,item)
    values 
    (123456,'CompanyA','Item1'),
    (123456,'CompanyA','Item2'),
    (456123,'CompanyB','Item2'),
    (789123,'CompanyC','Item1');

The following query (and this is the answer):

select t.id, t.company, t.item
  from t
  left join (select id from t where item='Item2') t2
    on t.id=t2.id
 where t2.id is null;

gives

+--------+----------+-------+
| id     | company  | item  |
+--------+----------+-------+
| 789123 | CompanyC | Item1 |
+--------+----------+-------+
1 row in set (0.00 sec)

Ok, this is MySQL, but I assume SQL Server gives the same.

Referring to the link I gave, in this case table A (the left one) is your complete table and table B (the other one) holds all IDs for which item='Item2'. If you simply left join them, you would get:

mysql> select t.id, t.company, t.item, t2.id as id2
         from t
         left join (select id from t where item='Item2') t2
           on t.id=t2.id;

+--------+----------+-------+--------+
| id     | company  | item  | id2    |
+--------+----------+-------+--------+
| 123456 | CompanyA | Item1 | 123456 |
| 123456 | CompanyA | Item2 | 123456 |
| 456123 | CompanyB | Item2 | 456123 |
| 789123 | CompanyC | Item1 |   NULL |
+--------+----------+-------+--------+
4 rows in set (0.00 sec)

because for the last line there is no row in the second table that has item='Item2'. Thus we add where t2.id is null and only get the desired lines.