One to Many Query Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

One to Many Query Issue

Author

Topic

n.o.shifflett
Starting Member

2 Posts

Posted - 2015-04-26 : 18:22:25

Attribute A in table A has a one to many relationship with attribute B in table A, I want to return all records for attribute A that do not contain a specific value for attribute B.
Example
Attribute A='ID_NUM' and Attribute B='ID_STATUS'
ID_NUM ID_Status
123 5
123 6
123 6
123 10
I want to return any value for ID_NUM that does not ever contain the value of '8' in ID_STATUS so in this example ID_NUM='123' would be a result.

n.o.shifflett
Starting Member

2 Posts

Posted - 2015-04-26 : 18:31:12

I hope this makes sense what I am trying to do.

Select ID_NUM
from tableA
where ID_STATUS<>'8'

The results of this query gives all ID_NUM values that have any row that does not contain an ID_STATUS='8' and I want only ID_NUM values where no ID_STATUS value ='8'

Kristen
Test

22859 Posts

Posted - 2015-04-27 : 07:14:37

If I have understood correctly maybe this?


SELECT *
FROM Table_A AS A
WHERE NOT EXISTS
(
    SELECT *
    FROM Table_A AS B
    WHERE     B.ID_NUM = A.ID_NUM
          AND B.ID_STATUS = 8
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources