Subquerying inside a LIKE statement? 1

[mpspillers]

Hi! I'm making the big switch from MS Access to SQL, and I'm having a hard time translating one of my old Access queries to SQL syntax.

I have 2 tables, [tblPerson] and [tblTitles]. I want to see every record in [tblPerson] where the [tblPerson].[role] field CONTAINS (is LIKE) any of the [title] fields in my [tblTitles] table (multiple records). Here is what it looked like in MS Access:

SELECT tblPerson.LastName, tblPerson.FirstName, tblPerson.Role
FROM tblPerson, tblTitles
WHERE (((tblPerson.Role) Like "*" & [tblTitles].[title] & "*"))
GROUP BY tblPerson.LastName, tblPerson.FirstName, tblPerson.Title;

(the group-by prevents the cross-join from returning dupe results for some of the tblPerson records.)

Worked like a charm in MS Access, but there are so many differences in SQL syntax with this type of query that I'm having a VERY hard time translating. I believe I need to place a subquery inside my LIKE wildcards. Is the same effect even possible in SQL?

Any advice would be much appreciated!

Michael

 

[PHV]

Why not simply replace this:
WHERE (((tblPerson.Role) Like "*" & [tblTitles].[title] & "*"))
with this ?
WHERE tblPerson.Role Like '%' + tblTitles.title + '%'

And, as you asked in the ANSI SQL forum:
SELECT DISTINCT P.LastName, P.FirstName, P.Role
FROM tblPerson P INNER JOIN tblTitles T
ON P.Role LIKE '%' || T.title || '%'


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mpspillers]

Thanks for your quick reply! That definitely starts me on the right track.

However I'm new to the "double pipe" thing.

||

When I used this in my query, SQL told me:

Incorrect syntax near '|'.

?

Appreciate it...
Michael

 

[PHV]

It's just because SQL Server is NOT ANSI compliant ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mpspillers]

Hmm, so does that mean I won't be able to use it? (I'm building a .Net webpage to query our SQL 2000 server...)

Michael

 

[PHV]

I thought I gave you an answer specific to your RDBMS above the ANSI syntax ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

BTW, the concatenation operator (|| in ANSI SQL) is + in T-SQL ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mpspillers]

Yes, your first option was one of my original attempts:

SELECT tblPerson.LastName, tblPerson.FirstName, tblPerson.Role
FROM tblPerson, tblTitles
WHERE tblPerson.Role Like '%' + tblTitles.title + '%'
GROUP BY tblPerson.LastName, tblPerson.FirstName, tblPerson.Role;

It does not error, but it returns 0 records. (My Access syntax returns the correct 8170 records.)

I'm wondering if I should use IN or EXISTS in some way to subquery thru all the records in tblTitles?

 

[r937]

i have to ask... if you return only the person's name and title, how do you know which title they match?

anyhow, if you're getting 0 rows, then something else is wrong, because the query syntax is okay

how about showing a couple of rows from each table? (just the columns involved in the query will suffice)

i betcha one of the tables is empty...



r937.com | rudy.ca

 

[mpspillers]

Because it's a smallish list of 22 titles that the users are very familiar with. When they scan thru the results, they can see that the Person Roles contain variations of the titles they are looking for. They would probably consider it redundant and/or confusing if I included another column showing the title from their familiar list that the person.role contains.

I'm trying to cross-join with a title table so that I'm not constantly digging thru hardcoded "OR... OR... OR..." statements whenever the users make a change to this list of titles.

Neither table is empty. I'd be happy to provide sample rows, but I don't know how to post delimited columns

tblPerson has 67793 records.
tblTitles has 22 Records.
The MS Access query returns 8170 wild-carded matches.
The SQL version returns 0.

 

[r937]

PERSONS
howard, curly, programmer
fine, larry, violinist
howard, moe, bandleader

TITLES
laborers and pipefitters
programmers and analysts


i was hoping you'd pick actual values so that we could see that they actually should match (perhaps even set up test tables ourselves to test the query)



r937.com | rudy.ca

 

[PHV]

At least 2 things to consider:
1) Acces is case insensitive in strings comparaison
2) Access suppress trailing spaces from fields

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mpspillers]

Ahh, that had EVERYTHING to do with it. I checked my Titles table and almost EVERY record contained a trailing space!

The case sensitive thing is still a bit of a problem, and harder to fix in the 67793 Person records. Is there a way to enforce "case insensitivity" in my SELECT statement?

 

[PHV]

Here an ANSI SQL answer.
It's up to you to translate in T-SQL:
SELECT DISTINCT P.LastName, P.FirstName, P.Role
FROM tblPerson P INNER JOIN tblTitles T
ON UPPER(P.Role) LIKE '%' || UPPER(TRIM(T.title)) || '%'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mpspillers]

Works beautifully, thanks for all the help!