SQL Syntax Question - LIKE Operator

All,

One of my first projects was to collect a sum of quantity of a particular type of item that we have in our database. The problem with this was the item's description was the ONLY way I can SURELY obtain the correct data.

For example, we sell safety harnesses at my job. Each safety harness has multiple components, and even more sub-components (and sub-components of sub-components and so on...). To collect ONLY the restraints that we sold, I had to resort to using the Description column of the Items table using the LIKE operator in my SQL query. Here is an example:

Code:

---

SELECT SUM(TItems.Item)
FROM TItems
WHERE TItems.Description LIKE '%restraint%'

---

And that could have been okay...but they wanted more specific types of restraints (like for Aviation, Ambulances, etc)...and once again I had to use a LIKE operator in the SQL query like:

Code:

---

SELECT SUM(TItems.Item)
FROM TItems
WHERE TItems.Description LIKE '%restraint%' OR TItems.Description LIKE '%kit%'

---

But the more criteria I was getting, the bigger the query kept getting (and it wasn't dynamic). There is NO other way to collect this info the way they are asking for it, and I've mentioned it to them.

So I thought I could come up with a table similar to:

Code:

---

tCriteria (name of table)
MyCriteria
*restraint*
*kit*

---

And use a syntax like this:

Code:

---

SELECT SUM(TItems.Item)
 FROM TItems, tCriteria
 WHERE TItems.Description LIKE tCriteria.MyCriteria

---

Which did work...but how does it work? I'm just a little confused on this...because if, for example, you have in the table:

Code:

---

tCriteria (name of table)
MyCriteria
*restraint*
*restraint*

---

It will double the sum for Descriptions that contain the word "restraint" in it...so does that mean my original table:

Code:

---

tCriteria (name of table)
MyCriteria
*restraint*
*kit*

---

Would double the sum of something like:

Description:
5-pt,restraint,kit,blah,blah,blah

:dunno


Also, how would anybody else approach this?

Quote:

---

Originally Posted by malik641

Also, how would anybody else approach this?

---

I would use IN with a Subselect. .02

How do you use IN with the LIKE operator?

Quote:

---

Originally Posted by malik641

How do you use IN with the LIKE operator?

---

Code:

---

SELECT [field] from T1 WHERE [Field] IN (SELECT [Field] FROM T2 WHERE T2.[Field1] LIKE [something] OR LIKE [somethingelse] OR....)

---

or use

Code:

---

WHERE EXIST

---

or even better, write a 'contains' UDF [if using Access] and call it directly.

Quote:

---

Originally Posted by stanl

Code:

---

SELECT [field] from T1 WHERE [Field] IN (SELECT [Field] FROM T2 WHERE T2.[Field1] LIKE [something] OR LIKE [somethingelse] OR....

---

)

---

But the problem with that is what I was mentioned before about writing a bunch of "OR LIKE"'s.

The way the WHERE clause in the following query:

Code:

---

SELECT SUM(TItems.Item)
 FROM TItems, tCriteria
 WHERE TItems.Description LIKE tCriteria.MyCriteria;

---

Works now is:

Code:

---

WHERE TItems.Description
LIKE [Row 1 of tCriteria.MyCriteria]
OR LIKE [Row 2 of tCriteria.MyCriteria]
OR LIKE [Row ... of tCriteria.MyCriteria]
OR LIKE [Row n of tCriteria.MyCriteria]

---

Quote:

---

Originally Posted by Stanl

or even better, write a 'contains' UDF [if using Access] and call it directly.

---

I will have to look into this. I'm not so familiar with it.

as I first said, I think you need a subselect. If indeed your criteria are in a table then perhaps a SELECT WHERE LIKE... SELECT ( IN() ) or other variations could be used. This will be an Access Select, or SQL Server [where Transact-SQL offers more features]?

Stan

I'll give it a shot.

Malik,

If you have multiple criteria, it makes sense to use the second table and either do:

Code:

---

Select Sum(Table.Items)
From Table
Where TI.[CriteriaField] in (Select CriteriaColumn FRom CriteraTable)

---

You can also link using a join

Code:

---

Select Sum(Table.Items)
From Table inner join CriteriaTable on Table.FieldName = CriteriaTable.Criterea

---

Which would only return the sum of matching rows where the criteria field in Table matches the values in the Criteria table.

You can also use dynamic SQL, but with that many variables and LIKE syntax..it gets "messy"