Using like together with in

[Larshg]

Hi

I'd like to compint an in with an like

this is the SQL
select sch_name "from", fol_sch_name "too"
from SNDOPERpprog_schfol
where sch_name in (
select oppar_job_name from SNDOPERppar
where (oppar_run_time in ('1'))
)


I'd like some thing like this
select sch_name "Afhænnighed fra", fol_sch_name "Afhænnighed til"
from SNDOPERpprog_schfol
where sch_name in LIKE %(
select oppar_job_name from SNDOPERppar
where (oppar_run_time in ('1','2')%)
)

Hope some one can help

/Lars

 

[carp]

You are trying to combine two comparators. You cannot do this in the same SQL statement.

 

[rac2]

Seems like you have job names that include the names of schools; and you want a list of the schools with jobs that have oppar_run_time 1 or 2.

So the oppar_run_time jobs of interest are these. I am omitting the table owner to simplify things.

SELECT oppar_job_name
FROM oppar
WHERE oppar_run_time IN ('1', '2')

Given a particular school name 'Olav Brem Elementary' we could find the jobs for that school like so.

SELECT oppar_job_name
FROM oppar
WHERE oppar_run_time IN ('1', '2')
  AND PATINDEX('%Olav Brem Elementary%', oppar_job_name) > 0

Another way to think about this result is that it will yield rows if there are 1 or 2 jobs at that school, otherwise it will yield 0 rows affected. Remember this.

The name of the school is obtained by making this a correlated sub-query of a main query which provides the names of schools.

SELECT *
FROM opprog_schfol

This table provides the school name and a whole lot more. The SELECT list need not include the school name for this to work, but as it happens you want the school name.

SELECT sch_name AS "from", fol_sch_name AS "too"
FROM opprog_schfol
WHERE EXISTS (
              SELECT *
              FROM oppar
              WHERE oppar_run_time IN ('1', '2')
                AND PATINDEX('%' + opprog_schfol.sch_name + '%', oppar_job_name) > 0

)

This query uses the EXISTS condition. If the subquery yields any rows, then the condition is true. It yields rows when there are jobs with the name of the school.

Correlated subqueries are executed for each row in the main query; each row in the main query supplies a different value for the subquery to test.

Hope I did not confuse things by talking about schools and jobs. Let me know how this works for you.

 

[rrrkrishnan]

Lars, you can try the 'like' operator with the quantifier 'any'. Not sure if this is ANSI SQL though.

select sch_name "Afhænnighed fra", fol_sch_name "Afhænnighed til"
from SNDOPERpprog_schfol
where sch_name LIKE ANY (
select oppar_job_name from SNDOPERppar
where (oppar_run_time in ('1','2'))
)