Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Conditional SQL SELECT

Posted on 2011-05-08
5
Medium Priority
?
262 Views
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.
Ta
0
Comment
Question by:Bradburt
  • 3
5 Comments
 
LVL 2

Accepted Solution

by:
abbas_najafizadeh earned 1000 total points
ID: 35715557
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"
from
TBL1,TBL2,TBL3
where
...
;


be success
0
 

Author Comment

by:Bradburt
ID: 35715599
Hi,
Not quite, TBL2.field1,2,3 have to be indexed from a foreign key in TBL1.
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 35719485
Please explain a bit more
0
 

Author Comment

by:Bradburt
ID: 35719504
Hi,
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.
0
 

Author Closing Comment

by:Bradburt
ID: 35719515
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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 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