Conditional SQL SELECT

Posted on 2011-05-08
Last Modified: 2012-05-11
I am adding a column to table TBL1. Each row of TBL1 will have a new value, no gaps and no extra rows.
I need to create the new column with each row holding its value as either;
IF( TBL2.bool_field1 ) THEN TBL3.A+TBL1.T
IF( TBL2.bool_field2 ) THEN TBL3.B+TBL1.T
IF( TBL2.bool_field3 ) THEN TBL3.C+TBL1.T
TBL2 and TBL3 rows are indexed by FK2, FK3 held within TBL1.
(There are three boolean field. I may be able to change those fields to a single field with one of three values but would rather not)

I cannot quite get the SELECT .. CASE statement to do this.
I am looking for a simple single statement SQL statement.
The statement should include the condition above plus a token column from another table.
Question by:Bradburt
    LVL 2

    Accepted Solution

    select TBL1.*,
    (case when TBL2.bool_field=true then TBL3.A+TBL1.T
    when TBL2.bool_field2=true THEN TBL3.B+TBL1.T
    when TBL2.bool_field3=true THEN TBL3.C+TBL1.T
    else TBL3.X+TBL1.T
    end) as "yournewcolumn"

    be success

    Author Comment

    Not quite, TBL2.field1,2,3 have to be indexed from a foreign key in TBL1.
    LVL 8

    Expert Comment

    Please explain a bit more

    Author Comment

    I have figured this out myself, the first answer was a starting point and I needed to add the foreign keys myself.
    I will accept the first answer although it was not fully detailed.

    Author Closing Comment

    I had to figure the FK part myself. Not being familiar with SQL I did not understand that it was easy as WHERE FK=value, FK2=value etc.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now