January 19, 2006 at 8:17 am
i have this query:
SELECT strMajorRoad, COUNT(idSCOOTChecklist) AS COUNT
FROM tblScootChecklist,
(SELECT ID AS agc
FROM AGSSITE
WHERE Descriptor LIKE % tblScootlist.strMajorRoad % ) AS ABC
WHERE ABC.agc = intIntersection
the part between the % % is the problem... how do i use a wildcard for a column in another table.
plz help.
January 19, 2006 at 9:03 am
'...how do i use a wildcard for a column in another table'
I assume that you are trying to compare AgsSite.Descriptor values to the values in tblScootlist.strMajorRoad? If this is the case, you cannot do it using wildcards. Try using a sub query like this:
SELECT strMajorRoad, COUNT(idSCOOTChecklist) AS COUNT
FROM tblScootChecklist,
(SELECT ID AS agc
FROM AGSSITE
WHERE Descriptor LIKE (Select strMajorRoad from tblScootlist) ) AS ABC
WHERE ABC.agc = intIntersection
January 19, 2006 at 11:52 am
There is no need for a sub-query:
SELECT s.strMajorRoad, COUNT(s.idSCOOTChecklist) AS COUNT
FROM tblScootChecklist As s
INNER JOIN AGSSITE As agc
On (agc.ID = s.intIntersection)
WHERE CharIndex( s.strMajorRoad, agc.Descriptor ) > 0
GROUP BY s.strMajorRoad
January 20, 2006 at 12:25 am
thanks PW.... it worked.
am new to this.. n so just need to figure your logic out... and use the it in other places.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply