Is it possible to use `IF THEN` without using ELSE in MySQL?-CodePudding

I need to write a query where user1 requests to display the HealthStatus of user2, however, before I display it, I need to ensure that user2 trusts user1 I have a table UserInfo where each user has a HealthStatus and a table Trusted where if ID1 and ID2 are in the same row then ID1 trusts ID2

I tried writing the following:

IF (EXISTS (SELECT ID1 FROM Trusted WHERE ID2=2010123 AND ID1=2009123))
THEN SELECT HealthStatus FROM UserInfo WHERE ID=2009123

Note: here ID2 is the ID of the user executing the query (can get it easily, I am doing this in Java) and ID1 is the ID of the user whose HealthStatus is requested to be disclosed.

Logic I am trying to apply:

"If there is any entry in the Trusted table such as the first ID is the one of the user whose HealthStatus is being requested and the second ID is the one of the user executing the query then do this"

I also tried:

IF (EXISTS (SELECT ID1 FROM Trusted WHERE ID2=2010123 AND ID1=2009123))
BEGIN
THEN SELECT HealthStatus FROM UserInfo WHERE ID=2009123
END

In both of these, the SQL Workbench underlines the IF statement and says "IF" is not valid at this position, expected EOF, Alter, Analyze, etc..

I haven't seen any statements with IF being used without ELSE so that might be it, any help in doing this query would be appreciated

CodePudding user response：

I think you want:

SELECT HealthStatus
FROM UserInfo
WHERE ID = 2009123 AND
      EXISTS (SELECT 1 FROM Trusted WHERE ID2 = 2010123 AND ID1 = 2009123);

If the exists clause above fails, the entire result set will be empty.