Using LIKE before the where clause

[Juice05]

Is it possible for me to write a SELECT statement where I have a LIKE before the WHERE clause? i.e.

SELECT GENERAL_INFO.INCIDENT_NUMBER, CAUSE.CAUSE_FACTORS (CAUSE.CAUSE_FACTORS LIKE 'H%') FROM GENERAL_INFO, CAUSE WHERE GENERAL_INFO.INCIDENT_NUMBER = CAUSE.INCIDENT_NUMBER

I WANT ALL OF THE INCIDENT_NUMBERS BUT ONLY HAVE THE CAUSE_FACTORS THAT ARE LIKE 'H%' TO BE DRAWN FROM THE CAUSE_FACTORS FIELD?

 

[carp]

No.

However, if I understand your requirement correctly,

SELECT GENERAL_INFO.INCIDENT_NUMBER, CAUSE.CAUSE_FACTORS
FROM GENERAL_INFO, CAUSE
WHERE GENERAL_INFO.INCIDENT_NUMBER = CAUSE.INCIDENT_NUMBER
AND CAUSE.CAUSE_FACTORS LIKE 'H%';

should give you what you want.

 

[Juice05]

That statement will get me all INCIDENT_NUMBERs where CAUSE_FACTOR LIKE 'H%', I need all of the incidents but I only want to return the cause factors that begin with H. This is for reporting and the cause factors are a many to one. I want to bring back only the ones that start with H and the rest will be NULL.

 

[carp]

Aha! OK, how about this:

SELECT GENERAL_INFO.INCIDENT_NUMBER, CAUSE.CAUSE_FACTORS
FROM GENERAL_INFO, CAUSE
WHERE GENERAL_INFO.INCIDENT_NUMBER
= CAUSE.INCIDENT_NUMBER
AND CAUSE.CAUSE_FACTORS LIKE 'H%'
UNION
SELECT GENERAL_INFO.INCIDENT_NUMBER, NULL
FROM GENERAL_INFO, CAUSE
WHERE GENERAL_INFO.INCIDENT_NUMBER
= CAUSE.INCIDENT_NUMBER
AND CAUSE.CAUSE_FACTORS NOT LIKE 'H%';

 

[EnglishMan]

Juice05,

What DBMS as you using? Do you have the IIF statement then I think I have a solution that will work.

Table 1 contains the following data

a hill
b car
c hop
d hill
e cat
f apple

Table 2 contains the following data

hill
top
car
hop

Then by using the following query, all rows are returned from table1 but only the rows from table1 that have hop or hill show data from table2.

SELECT Table1.a, Table1.b, iif(table2.a like 'h*',table2.a, null)
FROM Table1 LEFT JOIN table2 ON table1.b = table2.a;

The query returns the following ..

a b Expr1002
-------------------------
a hill hill
b car
c hop hop
d hill hill
e cat
f apple

Englishman.

 

[carp]

With Oracle, you could do the same thing with DECODE.