CODEWITHSUNDEEP
sqlt-sqlconditional-statements
DVNT
DVNT

Reputation: 61

Needing multiple where clauses or a better way of conditional statement

I'm trying to figure out how to show data in one table based on if a value doesn't exist in another table. I've researched case statements and couldn't figure out how it would apply in my current situation.

I have two tables, CompanyRequestOff and TakeOver and I want to be able to select columns from CompanyRequestOff where TransactionIDs are equal BUT the User column cannot be the same.

Any help even pointing me in the right direction would be useful thank you.

The CompanyRequestOff table has these columns:

TransactionID
Requestee
RequestedDate
RequestedTimeSlot
RequestedPosition
RequestStatus
LastUpdate
Manager

And the TakeOver table has:

Id
TransactionID
RequestedUser
RequestStatus
LastUpdate
Manager

This is my attempt at solving this:

SELECT
    CompanyRequestOff.TransactionID, CompanyRequestOff.Requestee,
    CompanyRequestOff.RequestedDate, CompanyRequestOff.RequestedTimeSlot,
    CompanyRequestOff.RequestedTimeSlot, CompanyRequestOff.RequestedPosition,
    CompanyRequestOff.RequestStatus, CompanyRequestOff.LastUpdate, 
    CompanyRequestOff.Manager 
FROM
    CompanyRequestOff, TakeOver 
WHERE
    RequestedDate > GETDATE() 
    AND CompanyRequestOff.RequestStatus = 'Requested' 
    AND CompanyRequestOff.TransactionID = TakeOver.TransactionID  
    AND TakeOver.RequestedUser != @UserN;

The only column that is related is the transactionID column which is primary in CompanyRequestOff and Foreign in TakeOver. I would like to display the columns that are in the CompanyRequestOff table where there is no row in TakeOver with that transactionID BUT only if it contains a certain TakeOver.RequestedUser value.

Upvotes: 0

Views: 63

Answers (2)

Aaron Dietz
Aaron Dietz

Reputation: 10277

It sounds like you want to join these two tables under two conditions:

SELECT *
FROM CompanyRequestOff CRO
JOIN TakeOver TO on TO.TransactionID = CRO.TransactionID and TO.User <> CRO.User

Update to account for your edits:

I would like to display the columns that are in the CompanyRequestOff table where There is no row in TakeOver with that transactionID BUT only if it contains a certain TakeOver.RequestedUser value.

You can achieve the transactionID part of this with a where transactionID not in clause:

SELECT *
FROM CompanyRequestOff
WHERE TransactionID not in 
     (SELECT TransactionID FROM TakeOver)

Then add in the User part:

SELECT *
FROM CompanyRequestOff
WHERE TransactionID not in 
    (SELECT TransactionID FROM TakeOver WHERE RequestedUser <> @UserN)

And with all your logic from your query:

SELECT *
FROM CompanyRequestOff
WHERE TransactionID not in 
    (SELECT TransactionID FROM TakeOver WHERE RequestedUser <> @UserN)
AND RequestedDate > GETDATE()
AND RequestStatus = 'Requested'

Upvotes: 2

hashbrown
hashbrown

Reputation: 3516

Looking at your question, it seems like you have two common columns between CompanyRequestOff and TakeOver - User and TransactionID. So, your query should be such that you select all the records from CompanyRequestOff where TransactionID is matching but user is not matching.

select c.* from CompanyRequestOff c, TakeOver t
where c.TransactionId = t.TransactionId 
and c.user != t.user

Upvotes: 0

Related Questions