CODEWITHSUNDEEP
sqlsql-server
Jayson
Jayson

Reputation: 83

SQL Query Single table with multiple results for same id

I have a table in SQL 2012 that has multiple lines for the same ID such as you see below: 

ID       Company       Item     
123456   CompanyA      Item1
123456   CompanyA      Item2
456123   CompanyB      Item2
789123   CompanyC      Item1

Each ID/Company can have multiple lines in this table. I need to query the table and show any record that doesn't have ItemX and also ignore any other record the company has? So in this example, CompanyA with ID 123456 has Item1 and Item2 but I don't want to see Item2 at all so I need my results to come out like this:

ID       Company       Item     
789123   CompanyC      Item1

Thanks.

Jayson

Upvotes: 0

Views: 1864

Answers (6)

PerlDuck
PerlDuck

Reputation: 5720

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.

Upvotes: 0

Shailesh
Shailesh

Reputation: 1

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'

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

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

Upvotes: 0

MondoDB
MondoDB

Reputation: 11

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
)

Upvotes: 1

Abhilash R Vankayala
Abhilash R Vankayala

Reputation: 693

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'

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

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

Upvotes: 0

Related Questions