MySQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMySQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old January 27th, 2013, 10:19 PM
ericldahl ericldahl is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2013
Posts: 1 ericldahl User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 31 m 18 sec
Reputation Power: 0
Subject: Using IF THEN ELSE to choose between two SELECT queries for a picklist

I need help with the SELECT statement in an application that I will call 'app_Update_Users' that I am developing using the ScriptCase RAD. This application is designed to update records in a Users table. The application enables the operator to assign each user to an organization by making reference to the following tables:
  • States
  • Counties
  • Organizations
  • Users

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 (because my client's service area is limited to 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 on a picklist based on the state in which each organization is located and the county or counties in which it provides 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:

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


For the uCounty field:

Code:
    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:

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:

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:

Code:
    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?

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMySQL Development > Subject: Using IF THEN ELSE to choose between two SELECT queries for a picklist


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap