PDA

View Full Version : SQL Syntax Question - LIKE Operator



malik641
10-15-2007, 02:27 PM
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:


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:


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:

tCriteria (name of table)
MyCriteria
*restraint*
*kit*
And use a syntax like this:


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:

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:


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?

stanl
10-15-2007, 04:24 PM
Also, how would anybody else approach this?

I would use IN with a Subselect. .02

malik641
10-15-2007, 04:28 PM
How do you use IN with the LIKE operator?

stanl
10-16-2007, 04:04 AM
How do you use IN with the LIKE operator?

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

or use WHERE EXIST

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

malik641
10-26-2007, 05:46 PM
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:

SELECT SUM(TItems.Item)
FROM TItems, tCriteria
WHERE TItems.Description LIKE tCriteria.MyCriteria; Works now is:

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]



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.

stanl
10-27-2007, 07:06 AM
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

malik641
10-27-2007, 06:33 PM
I'll give it a shot.

XLGibbs
10-28-2007, 03:21 PM
Malik,

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

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


You can also link using a join

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"