SSMS deleting/altering data from rows that are false to a condition-CodePudding

The question is being changed to : How to show values for a true conditioned column only?

For reference info this will stay but soon this will be deleted

Asked :

Show all Users and Passwords but for users which sent message to User99 Also show Room . This is the base:

SELECT  u.UserID, u.Password
        m.RoomID
FROM User as u
LEFT JOIN Message as m
    ON u.UserID = m.UserID

You also have ToUserID Col. - Which means to which user did they send a massage. In our case the Col. identify who sent message to User99 and than you asked to show ( for those users who sent message to User99 ONLY ) the Room. You can delete all the other rows from the col. / alter them by leaving them empty.

YOU CAN IGNORE THIS PART IF IT'S UNCLEAR and the question is. this is what I've tried so far:

Putting WHERE filters the tables before joining and doesn't cancel the rows for non "99 Room Message Users" as shown :

SELECT  u.UserID, u.Password
        m.RoomID
FROM User as u
LEFT JOIN Message as m
    ON u.UserID = m.UserID 
WHERE m.ToUserID = 99

Also putting the condition on the ON as shown:

SELECT  u.UserID, u.Password
        m.RoomID
FROM User as u
LEFT JOIN Message as m
    ON u.UserID = m.UserID and m.ToUser = 99

(I'd like that if you can add explanation to what's the different between the ON/WHERE )

Suggestion which I don't know how to made happen I need condition which only apply when the other condition applies

"m.ToUser Id != 99 (Then) SUBSTRING(Room ,0 ,0)"

ADDED info as requested :

SELECT  e.EmployeeID, e.BirthDate,
        o.ShipCity
      
FROM Employees as e
LEFT JOIN Orders as o
    ON o.EmployeeID = e.EmployeeID

Lets say that I wanted to leave the results as it is and only where employee have Order number of XXX ( the Col name is o.OrderID ) the Col o.ShipCity will show his Ship city I hope it's clearer

Here's the results set for this code

EmployeeID  BirthDate               ShipCity
----------- ----------------------- ---------------
5           1955-03-04 00:00:00.000 Reims
6           1963-07-02 00:00:00.000 Münster
4           1937-09-19 00:00:00.000 Rio de Janeiro
3           1963-08-30 00:00:00.000 Lyon
4           1937-09-19 00:00:00.000 Charleroi
3           1963-08-30 00:00:00.000 Rio de Janeiro
5           1955-03-04 00:00:00.000 Bern

I'll edit this post again as it takes time as I'm aware I'm lacking the ability to explain well . Thank you for your patience

CodePudding user response：

Hi you need to remove the users of room 99.

SELECT  u.UserID, u.Password FROM User as u
  where u.UserID not in (select m.UserID from Message as m WHERE m.ToUserID=99);

CodePudding user response：

How to show values in Col3 where Col1 values are true and for false values in Col1 show NULL in Col3 only

Answer on this page