CODEWITHSUNDEEP
mysqlselectif-statement
user1814188
user1814188

Reputation: 41

Using IF … THEN … ELSE to choose between two SELECT queries

I need help with the SELECT statement in an application that I will call 'app_Update_Users'. This application is designed to update records in a Users table. The application code makes reference to the following tables:

The States table contains a list of all 50 US states. The Counties table contains a list of the 58 counties in the state of California. The Organization table contains a list of organizations that may be located in any state and that provide services in one or more California counties. When adding or updating a User record, I want to filter the list of Organizations based on the state in which they are located and the county or counties in which they provide services.

The application has three select-type statements to create picklists of attributes in a Users record. I have written code that works for the first two statements, but I am having problems with the code for the third statement. The fields referenced by these statements are: - uState - uCounty - uOrganization

The statements for the uState and uCounty fields invoke onChange Ajax processing to update the selections on the picklist for the uOrganization field. The options for the picklists for first two fields are selected with the following code:

For the uState field:

SELECT stAbbr, CONCAT(stAbbr, ' - ', stStatePK)
   FROM States 
   ORDER BY stAbbr;

For the uCounty field:

SELECT cCounty, cCounty 
   FROM Counties 
   ORDER BY cCounty;

The records in the Organizations table can have multiple counties listed in the oCounties field because an organization can operate in multiple counties. Therefore the options for the picklist for the uOrganization field are currently selected with the following code: 

SELECT oName
   FROM Organizations 
   WHERE oCounties LIKE '%{uCounty}%' 
   ORDER BY oName ASC ;

I can also select records in the Organizations table based on the state in which they are located with the following code: 

SELECT oName, oName
   FROM Organizations 
   WHERE oState = '{uState}' 
   ORDER BY oName ASC ;

However, the logic that I want to use for selecting the options for the uOrganization field would combine these two code snippets. In plain English, it would go something like this: "If the uState field is set to 'California', then select records from the Organizations table based on the value in the uCounty field. IF the uState field is not set to 'California', then select records from the Organizations table based on the value in the uState field.

The following IF . . . THEN . . . ELSE syntax gets the point across, but it doesn't work:

IF uState = 'CA' THEN
   SELECT oName, oName
   FROM Organizations 
   WHERE oCounties LIKE '%{uCounty}%' 
   ORDER BY oName ASC ;  
ELSE
   SELECT oName, oName
   FROM Organizations 
   WHERE oState = '{uState}' 
   ORDER BY oName ASC ;
ENDIF

What is the correct way to write this code?

Upvotes: 4

Views: 3662

Answers (2)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can use if statment in where condition

SELECT 
    oName, 
    oName
FROM Organizations 
WHERE 
    IF(uState = 'CA' , oCounties LIKE '%{uCounty}%' , oState = '{uState}'  )
ORDER BY oName ASC ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can do this without the if with a single statement:

SELECT oName, oName
FROM Organizations 
WHERE (ustate = 'CA' and oCouties like '%{uCounty}%') or
      (uState <> 'CA' and oState = '{uState}')
ORDER BY oName ASC ;

Upvotes: 5

Related Questions