asked on

T-SQL - One to many exists clause

Tbl A column aRecordID.
Tbl B will have many corresponding records with aRecordID.
(Tbl A to Tbl B is one to many)

Tbl B has a column called productID. I need to grab all of the aRecordID's from Tbl A where ALL of the related records in Tbl B have productID = 1. If any of the related records in Tbl B has anything other than productID = 1, then I don't want the aRecordID from Tbl A.

Hope that makes sense. Thanks in advance!!

Vitor Montalvão

SELECT TblA.aRecordID, TblB.productID 
FROM TblA
    INNER JOIN TblB ON TblA.aRecordID=TblB.aRecordID
WHERE TblB.productID = 1

Open in new window

Lee

On what column do the tables relate?

Vitor Montalvão

Or since it will return duplicate records (one to many relationship) you can use the keyword DISTINCT to show less records:

SELECT DISTINCT TblA.aRecordID, TblB.productID 
FROM TblA
    INNER JOIN TblB ON TblA.aRecordID=TblB.aRecordID
WHERE TblB.productID = 1

Open in new window

Lee

Ahh, ignore me, I missed that.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rickzzz

ASKER

Thanks!

Scott Pletcher

That code does NOT meet your original conditions:

I need to grab all of the aRecordID's from Tbl A where ALL of the related records in Tbl B have productID = 1. If any of the related records in Tbl B has anything other than productID = 1, then I don't want the aRecordID from Tbl A.

Vitor Montalvão

I think the author wanted to say that he only want records with ProductID=1 and not interested in others products. At least is what I understood.

Scott Pletcher

It's very clear that was NOT the original requirement:

If any of the related records in Tbl B has anything other than productID = 1, then I don't want the aRecordID from Tbl A.

That's perfectly clear, absolutely unambiguous.

Rickzzz

ASKER

Actually I just realized this should have gone to Scott Pletcher.

Rickzzz

ASKER

Yes, I selected the wrong one. Is there an admin that can fix.

Vitor Montalvão

Strange for not wanting the aRecordID from TblA (PK) since is the same value of aRecordID from TblB (FK).

Scott Pletcher

[Also no indication that "aRecordID" is a primary key; I won't make that assumption either. It could be, but that's not really known -- or relevant.]

Rickzzz

ASKER

Thanks, yes don't want anything from Tbl A unless the condition above exists.

Vitor Montalvão

(Tbl A to Tbl B is one to many)
(...)
I need to grab all of the aRecordID's from Tbl A where ALL of the related records in Tbl B have productID = 1. If any of the related records in Tbl B has (...)

For me this means that exists a relation between those tables by the column aRecordID.

Scott Pletcher

Of course, but that doesn't meant aRecordID is necessarily a "primary key" (PK), as you stated.