CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Mark Bucknell
Mark Bucknell

Reputation: 1

How to use an XOR like function in SQL

I cannot seem to get a grasp on the sql required to meet my needs for this question. SO far I have:

select * from customformresponses
INNER JOIN exhibitors ON
Exhibitors.ExhibitorId= customformresponses.ExhibitorId
WHERE customformresponses.exhibitorid='8179cde9-b922-430a-9024-bd4cb8b3d05c'
  and exhibitors.exhibitionID = 'e641a3d4-cb57-4f67-86a1-5c2f4c3cf6e0'
  and customformresponses.FormID = 'c7f5f0de-35f8-412d-9c91-eaf8bb1a3c26'

I need to search for responses in 3 different formID values but the problem im having is if I have:

customformresponses.FormID = 'c7f5f0de-35f8-412d-9c91-eaf8bb1a3c26'  or
customformresponses.FormID = 'c7f5f0de-35f8-412d-9c91-eaf8bb1a3c26' or
customformresponses.FormID = 'e69cee39-2519-434d-be3e-516ba156b444'

Then it only returns the results of the first true condition not any of the true conditions.

How do you return all the results from the rows that meet this criteria?

Upvotes: 0

Views: 640

Answers (3)

Andrew
Andrew

Reputation: 336

select * from customformresponses 
INNER JOIN exhibitors     
ON Exhibitors.ExhibitorId= customformresponses.ExhibitorId 
WHERE customformresponses.exhibitorid='8179cde9-b922-430a-9024-bd4cb8b3d05c' 
AND exhibitors.exhibitionID = 'e641a3d4-cb57-4f67-86a1-5c2f4c3cf6e0' 
AND ormresponses.FormID IN  ( 'c7f5f0de-35f8-412d-9c91-eaf8bb1a3c26', 'e69cee39-2519-434d-be3e-516ba156b444')

Upvotes: 0

Andriy M
Andriy M

Reputation: 77707

If only to avoid repeating the GUIDs and column names...

SELECT *
FROM customformresponses
  INNER JOIN exhibitors ON Exhibitors.ExhibitorId = customformresponses.ExhibitorId
WHERE (
  CASE customformresponses.exhibitorid WHEN '8179cde9-b922-430a-9024-bd4cb8b3d05c' THEN 1 ELSE 0 END +
  CASE exhibitors.exhibitionID WHEN 'e641a3d4-cb57-4f67-86a1-5c2f4c3cf6e0' THEN 1 ELSE 0 END +
  CASE customformresponses.FormID WHEN 'c7f5f0de-35f8-412d-9c91-eaf8bb1a3c26' THEN 1 ELSE 0 END
) = 1

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

I think this is what you are after

select *
from customformresponses
INNER JOIN exhibitors
    ON Exhibitors.ExhibitorId= customformresponses.ExhibitorId
WHERE customformresponses.exhibitorid='8179cde9-b922-430a-9024-bd4cb8b3d05c'
AND exhibitors.exhibitionID = 'e641a3d4-cb57-4f67-86a1-5c2f4c3cf6e0'
AND
( customformresponses.FormID = 'c7f5f0de-35f8-412d-9c91-eaf8bb1a3c26'
  OR
  customformresponses.FormID = 'e69cee39-2519-434d-be3e-516ba156b444')

It will require matching exhibitorid and exhibitionid, but for the FormID, it will match any of the two.

So if you have two rows

exhibitorid      | exhibitionid     | formid
8179cde9-b922... | c7f5f0de-35f8... | c7f5f0de-35f8-412d-9c91-eaf8bb1a3c26
8179cde9-b922... | c7f5f0de-35f8... | e69cee39-2519-434d-be3e-516ba156b444

Both of them will be in the output resultset

Upvotes: 1

Related Questions