CODEWITHSUNDEEP
sqlsql-server-2008selectjoin
Matt Weick
Matt Weick

Reputation: 332

Select Data in SQL from No links between another table/column

Table and (columns) in question are:

Attachment (att_id)

Assignment (att_id, ctg_id, and itm_id)

I have tried for several hours to try and call the data I am looking for but to avail. I can't figure out the logic behind it and it seems so simple.

I need to call all the rows in the Attachment Table where the att_id is not linked to a ctg_id or itm_id in the Assignment table.

I make the join on att_id = att_id but that brings up all the rows in the Attachment table that are linked to a ctg_id or itm_id, when I need just the opposite.

Very Frustrating. Any advise/help is greatly appreciated.

Upvotes: 1

Views: 385

Answers (5)

Mathmagician
Mathmagician

Reputation: 113

What you want is called a Left Anti Semi Join.

SELECT *
FROM Attachment
WHERE NOT EXISTS (SELECT 1
                  FROM Assignment
                  WHERE Attachment.att_id = Assignment.att_id)

It's also a bit more efficient than using a regular left outer join, though SQL server will often be smart enough to figure that out.

Upvotes: 1

Mike
Mike

Reputation: 81

You may want to try an alternate just so the correlated sub-query doesn't slow you down.

create table Attachment (att_id int)
create table Assignment ( att_id int, ctg_id int, itm_id int)
insert into Attachment values( 100)
insert into Attachment values( 350)
insert into Attachment values( 7)
insert into Attachment values( 99)

insert into Assignment values ( 100,1,1)
insert into Assignment values ( 7,2,2)

--SELECT *
--FROM Attachment
--WHERE att_id NOT IN (SELECT att_id FROM Assignment)

SELECT ATT.*
FROM Attachment ATT LEFT outer join Assignment ASI on ATT.att_id = ASI.att_id
WHERE ISNULL(ASI.att_id,-1)=-1

drop table Attachment
drop table assignment

edit: LOL - as I typed this up two more of the same answers came in. Oh well.

Upvotes: 0

Negro Suave
Negro Suave

Reputation: 1

select * from attachment a 
left join assignment a2
where a2.ctg_id is null or a2.itm_id is null

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

A LEFT OUTER JOIN is an easy way to find non-matches:

select at.*
from Attachment at
left outer join Assignment as on at.att_id = as.att_id
where as.att_id is null

Upvotes: 2

Michael Dunlap
Michael Dunlap

Reputation: 4310

This should select all rows in Attachment that are not referenced by att_id in Assignment.

SELECT *
FROM Attachment
WHERE att_id NOT IN (SELECT att_id FROM Assignment)

JOIN is typically used to find links, not to find non-links. WHERE x NOT IN ([blah]) is used to find missing links.

Upvotes: 4

Related Questions