CODEWITHSUNDEEP
sql-server
Umer Waheed
Umer Waheed

Reputation: 4584

How to check the records with some value must exist in SQL server 2014?

I have a table "CarParties" having columns car_id(FK), partyId(FK) and source(bit).

Another table is "Party" having columns partyId,party_type.( It can be 1,2,3.)

  1. is buyer.

  2. is seller.

  3. is witness.

Now what I want is I want to check from carparties that there should be at least one party of buyer, one party of seller and one party of witness.

I can check it easily as below pseudo code of SQL.

Decalre @isValid as bit = 1
if(select Count(1) 
   From CarParties 
   Inner join partyType on partyType.partyId = CarParties.partyId 
   where car_id=1 and partyType =1 ) > 0
Then
   @isValid = 1
Else
   @isValid =0

Like above query i will check for each party and at the end i will select @isValid. (You can suggest another way to do it).

Now the twist is, i want to return true if it exists all the parties type with source = 1. Following are the scenario.

  1. Return True if all parties type exist and source is 1.
  2. Return False if all parties type exist and source is 0 for any party.
  3. Return False if there is no party.
  4. Return False if one or two party type exist and any one partytype is missing.

How I can do this?

Upvotes: 1

Views: 61

Answers (2)

dean
dean

Reputation: 10098

If party_type is limited to (1,2,3) and you want to check if all three are present, it's as easy as SUM:

select cp.car_id, sum(distinct p.partytype) 
from @carparties cp join @party p on cp.partyid = p.partyid and p.source = 0
group by cp.car_id 
having sum(distinct p.partytype) = 6

(notice I've used SUM(distinct...) to account for possible multiple entries of the same type)

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

You may looking for this

DECLARE @isValid AS BIT = 1 
IF EXISTS(SELECT 1 
           From CarParties C 
           INNER JOIN partyType  p1 on p1.partyId = C.partyId AND p1.partyType =1
           INNER JOIN partyType  p2 on p2.partyId = C.partyId AND p2.partyType =2
           INNER JOIN partyType  p3 on p3.partyId = C.partyId AND p3.partyType =3
           WHERE car_id=1 and SOURCE = 1)
THEN
  @isValid = 1
Else
  @isValid = 0

Upvotes: 1

Related Questions