Select 
	qm.id_oqm, 
	cast(isNull(id_eval, '') as varChar(50)) + ' - ' + qm.methodName as methodName, 
	qm.methodName as actualMethod,
	cv.* 
FROM 
	OQMethods QM 
left outer join 
	courseversions cv 
	on cv.evalid = QM.id_eval 
	and cv.courselanguage = 'EN' 
	and cv.courseactive='Y' 
	and cv.id_cp > 0 
	and QM.id_oqt = '26'
	and active = 1

UNION

Select 
		qm.id_oqm, 
		cast(isNull(id_eval, '') as varChar(50)) + ' - ' + qm.methodName as methodName, 
		qm.methodName as actualMethod,
		cv.* 
FROM 
	OQMethods QM 
inner join 
	courseversions cv 
	on cv.evalid = QM.id_eval 
	and cv.courselanguage = 'EN' 
	and cv.courseactive='Y' 
	and cv.id_cp > 0 
where 
	QM.id_oqt = '31' and active = 1

quote:

---

Originally posted by vmchase

I am having problems with this case statement. I don't know what I am doing wrong but I get the error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I have a case when the field equals a value then do a left outer join but if the field equals a different value then do a inner join. This is my query:

SELECT

case
when oqt = '26' then

(Select qm.id_oqm, cast(isNull(id_eval, '') as varChar(50)) + ' - ' + qm.methodName as methodName, qm.methodName as actualMethod,cv.*
FROM OQMethods QM left outer join courseversions cv on cv.evalid = QM.id_eval and cv.courselanguage = 'EN' and cv.courseactive='Y' and cv.id_cp > 0
WHERE QM.id_oqt in (SELECT tempoq.oqt FROM tempoq INNER JOIN OQMethods ON tempoq.oqt = OQMethods.id_oqt)and active = 1)
END,
case
when oqt = '31' then
(Select qm.id_oqm, cast(isNull(id_eval, '') as varChar(50)) + ' - ' + qm.methodName as methodName, qm.methodName as actualMethod,cv.*
FROM OQMethods QM inner join courseversions cv on cv.evalid = QM.id_eval and cv.courselanguage = 'EN' and cv.courseactive='Y' and cv.id_cp > 0
where QM.id_oqt in (SELECT tempoq.oqt FROM tempoq INNER JOIN OQMethods ON tempoq.oqt = OQMethods.id_oqt) and active = 1)

END
from tempoq

---

select a.is_oqm
      ,case(isnull(a.is_eval,'') as varchar(50))+' - '+a.methodname as methodname
      ,a.methodname as actualmethod
      ,c.*
  from oqmethods as a
       inner join tempoq as b
               on b.oqt=a.id_oqt
       left outer join courseversions as c
                    on c.evalid=a.is_eval
                   and c.courselanguage='EN'
                   and c.courseactive='Y'
                   and c.is_cp>0
       
 where a.active=1
   and case when c.evalid is null then 0 else 1 end>=case when b.oqt='31' then 1 else 0 end

quote:

---

Originally posted by vmchase

I maybe thinking to hard on this one,

But basically, I am selecting this:

Select qm.oqt, qm.id_oqm, cast(isNull(id_eval, '') as varChar(50)) + ' - ' + qm.methodName as methodName, qm.methodName as actualMethod,cv.*

FROM OQMethods QM

But it's the joins that are conditional-

If the qm.oqt = 26 then I will have a left outer join
left outer join courseversions cv on cv.evalid = QM.id_eval and cv.courselanguage = 'EN' and cv.courseactive='Y' and cv.id_cp > 0

But if the qm.oqt = 31 then I will have a inner join
inner join courseversions cv on cv.evalid = QM.id_eval and cv.courselanguage = 'EN' and cv.courseactive='Y' and cv.id_cp > 0

where QM.id_oqt in (SELECT tempoq.oqt FROM tempoq where tempoq.oqt = QM.id_oqt)and active = 1)

I just don't know how to script this in sql the correct way

---

where a.active=1
   and c.evalid in ('26','31')
   and case when c.evalid is null then 0 else 1 end>=case when b.oqt='31' then 1 else 0 end

quote:

---

Originally posted by vmchase

What I am trying to do is if the oqt = 26 then the join has to be an left outer join, but if it is 31- it has to be a inner join. All other oqt numbers it would just select everything from the tempoq table. I am not sure how to go about writing an if statement on this since I can have mulitple value.

---

where 
	active = 1
	AND
	(
		(
			QM.id_oqt = '31' 
			AND cv.id IS NOT NULL-- Some ID or value that is not nullable from the courseversions  table
		)
		OR
		(
			QM.id_oqt <> '31'
		)
	)

quote:

---

Originally posted by vmchase

Bitsmed solution worked for 26 and 31 but I need all the other values for OQT as well.

---

quote:

---

Originally posted by vmchase

What I am trying to do is if the oqt = 26 then the join has to be an left outer join, but if it is 31- it has to be a inner join. All other oqt numbers it would just select everything from the tempoq table. I am not sure how to go about writing an if statement on this since I can have mulitple value.

---