0

I am trying to select multiple fields from a case statement. I know the below syntax is wrong, but it describes what I am trying to do. Drawing particular attention to the CASE statement and the @type variable. Am I on the right track and if Im not some helpful poiters in the right direction would be appreciated.

Select
(tasks.id) As 'id',
(tasks.Title) As 'title',
(Excursions.ExcursionsColour) As 'backgroundColor',
(Excursions.ExcursionsColour) As 'borderColor',
CONCAT('taskcomplete.php?ex=',Excursions.idExcursions,'&id=',tasks.id) AS 'url',
CASE 
WHEN Excursions.typeID = '1' THEN
    date_sub(Excursions.ExcursionsStart, INTERVAL $tasks.Day_Incursion_days DAY)) As 'start',
    date_sub(Excursions.ExcursionsStart, INTERVAL $tasks.Day_Incursion_days DAY)) As 'end';
    SET @type = 'tasks.Day_Incursion';
WHEN '2' THEN
    tasks.Overnight_Interstate,
    date_sub(Excursions.ExcursionsStart, INTERVAL $tasks.Overnight_Interstate_days DAY)) As 'start',
    date_sub(Excursions.ExcursionsStart, INTERVAL $tasks.Overnight_Interstate_days DAY)) As 'end';
    SET @type = 'tasks.Overnight_Interstate';
WHEN '3' THEN
    tasks.International,
    date_sub(Excursions.ExcursionsStart, INTERVAL $tasks.tasks.International_days DAY)) As 'start',
    date_sub(Excursions.ExcursionsStart, INTERVAL $tasks.tasks.International_days DAY)) As 'end';
    SET @type = 'tasks.International';
END CASE;
From
  tasks,
  Excursions
Where
  @type = 'Y' And
  tasks.Sub_Task_Title = '' And
  ExcursionsUser = '$_SESSION[user]'

Thank you all... so I need to:

  • Split my case statements
  • run a sub query to get my type of excursion and apply it to my WHERE clause
  • Join my tables, thanks for pointing that out. I had forgotten

I will post an update shortly.

ash
  • 33
  • 4
  • I'm not sure why you think that a syntactically incorrect SQL statement would describe what you want to do. Sample data, desired results, and some explanation generally work much better than non-working code. – Gordon Linoff Jul 21 '14 at 00:40
  • Sorry Gordon, If I had the right syntax I wouldn't need to post a question;-) I thought this would be in preference to a lengthy text description. – ash Jul 21 '14 at 00:43
  • You appear to have no correlation connecting your two tables, which is unlikely - there should be (at least one) columns in the two tables that are equal to each other. You're also **wide** open to SQL Injection - use [parameterized queries](http://stackoverflow.com/a/60496/812837) or suffer the consequences. Most of your statement doesn't seem to require the `CASE` - the date math is the same in each branch (just use one variable) – Clockwork-Muse Jul 21 '14 at 00:46

1 Answers1

0

You canot return multiple fields from a single case statement. You need to repeat the case with the same condition, once for each field.

Also the SET inside tha case is wrong.

I think you are looking for something like this:

Select * from 
(
select
CASE Excursions.typeID 
WHEN '1' THEN
    tasks.Day_Incursion
WHEN '2' THEN
    tasks.Overnight_Interstate
WHEN '2' THEN
    tasks.International
end as type
from 
...
)
where type = 'Y'
;
lalborno
  • 414
  • 3
  • 5