Tiny little query problem

[webmigit]

I love generic joining.. makes my job so much easier.. I know how to say like...

Where table1.var1 = '#var2#'.. easy stuff, love it..

But.. I need to say WHERE table1.var1 LIKE table2.var2.. Every quote combination with * and % wildcards and all that I've tried doesn't work..

Table1.var1 has numerical values like this: ranging from 1 to around 1,300.. Table2.var2 has values like this 11_1,11_2.. The first digit ranges from 1 to 6. The second ranges from one to four, the underscore always remains in place.. the last digit ranges from 1 to 1300..

This is my exact query:

<cfquery name=&quot;getSchools2&quot; datasource=&quot;#dsn#&quot;>
SELECT schools.region, schools.division, schools.school, scouting.thisID
FROM schools,scouting
ORDER BY region, division, school
WHERE schools.school=%scouting.thisID%
</cfquery> [ Founder of <A href=&quot;

http://www.bibleclicks.com&quot;>Bibleclicks.com</A>,

online bible. ]

 

[snix1]

Hi! Shouldn't your query be this:


<cfquery name=&quot;getSchools2&quot; datasource=&quot;#dsn#&quot;>
SELECT schools.region, schools.division, schools.school, scouting.thisID
FROM schools,scouting
ORDER BY region, division, school
WHERE schools.school LIKE '%scouting.thisID%'
</cfquery>


Hope this helps.

 

[webmigit]

Uh.. that's telling it to look for the text scouting.thisID..

Anything in quotes tells it to look for the text, when marked with # it tells it to look for text that is defined from a variable.. But when its not quoted in anyway and its not marked with percent signs, it tells it dig into the databse.. another table if that's the request and get the value..

But thanks anyway! [ Founder of <A href=&quot;

http://www.bibleclicks.com&quot;>Bibleclicks.com</A>,

online bible. ]

 

[tleish]

A few questions:

1. What type of Database is being used?
2. In the example given above, does table2.var2 have a list ('11_1,11_2') in the each field, or a single value (field1 = '11_1', field2 = '11_2') in each field: - tleish

 

[webmigit]

Ah.. MS Access.. and each value is in a field of its own, thanks! [ Founder of <A href=&quot;

http://www.bibleclicks.com&quot;>Bibleclicks.com</A>,

online bible. ]

 

[tleish]

Another question... in the sql statement, do you want to match up schools.school with the number after the underscore?

Example:
schools.school (val = 230) matches scouting.thisID. (12_230) - tleish

 

[webmigit]

Yes, that's exactly it.. I think I may have a problem..

SELECT schools.region, schools.division, schools.school, schools.schoolID, scouting.thisID
FROM schools,scouting
WHERE schools.schoolID=scouting.GeneralID
ORDER BY region, division, school WORKS! but there's a problem.. I don't want generalID, I want ThisID.. but thisID won't work because its not a number field.. Is there a way I can convert it on the fly for a second.. or maybe a compatible data type with numbers? [ Founder of <A href=&quot;

http://www.bibleclicks.com&quot;>Bibleclicks.com</A>,

online bible. ]

 

[tleish]

Try:

<cfquery name=&quot;getSchools2&quot; datasource=&quot;#dsn#&quot;>
SELECT schools.region, schools.division, schools.school, scouting.thisID
FROM schools,scouting
WHERE scouting.thisID LIKE '%

[

_

]

' & schools.school
ORDER BY region, division, school
</cfquery> - tleish

 

[webmigit]

First, I wanna say thanks, second, I wanna say that if you've ever got a problem with a script or need some help in coding, Get ahold of me, and third I wanna ask if you're on AIM or MSN? (CLICKTHRUBIBLE|WEBMIGIT@HOTMAIL.COM)

Thanks!

Tony Hicks [ Founder of <A href=&quot;

http://www.bibleclicks.com&quot;>Bibleclicks.com</A>,

online bible. ]