• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

SQL question

I don't know why this query doesn't work as expected. I've tried two different versions:  

SELECT p.PK_PersonsID, p.MemberType_FK, p.LastName, p.FirstName, p.MiddleName, p.Address1, p.Address2, p.City, p.State, p.Zip, p.Phone, p.Fax, p.EMail, p.Company, m.MBREADesignation, m.MemberSince, m.MemTypeApprovalDate, m.Specialties, m.ProfileApproved, mt.PK_MemberTypeID, mt.MemberType
FROM tbl_persons p INNER JOIN tbl_members m ON p.PK_PersonsID = m.PersonsID_FK
INNER JOIN tbl_membertype mt ON p.MemberType_FK = mt.PK_MemberTypeID
WHERE p.City = '#session.city#' and #session.specialty# IN (m.Specialties)

SELECT p.PK_PersonsID, p.MemberType_FK, p.LastName, p.FirstName, p.MiddleName, p.Address1, p.Address2, p.City, p.State, p.Zip, p.Phone, p.Fax, p.EMail, p.Company, m.MBREADesignation, m.MemberSince, m.MemTypeApprovalDate, m.Specialties, m.ProfileApproved, mt.PK_MemberTypeID, mt.MemberTypeFROM tbl_persons p, tbl_members m, tbl_membertype mt
WHERE p.City = '#session.city#' AND #session.specialty# IN (m.Specialties) AND p.PK_PersonsID = m.PersonsID_FK AND p.MemberType_FK = mt.PK_MemberTypeID

The Specialties column in the tbl_members table contains comma-delimited lists of numbers. If I comment out the parts of the queries that look at that column, the query works (that is, it returns all the records in the city specified in session.city). But when I include the session.specialty criterion, no records are returned. I have verified that session.specialty contains a single number and that number does occur in some records in the table for which there is a corresponding record for the specified city in tbl_persons. I think an inner join is all that is required here since I only want records from tbl_persons for which there is a corresponding record in tbl_members. That is, the specialty criterion should be found first before looking for records in the other tabes.
      
0
brucecrabtree
Asked:
brucecrabtree
1 Solution
 
wytcomCommented:
Does #session.specialty# evaluate to a field name?

Seems like

WHERE p.City = '#session.city#' and #session.specialty# IN (m.Specialties)

should be

WHERE p.City = '#session.city#' and m.Specialties IN (#session.specialty#)

0
 
brucecrabtreeAuthor Commented:
m.Specialties is a list, like this: 3,5,7,8. It can be null or a list of 1 up to 10 numbers (numbers 1-10)

session.specialites is always a single number

so for example, if session.specialty is 5, then I'm looking for it to return all records where the Specialties column contains 5

so, if the table contains this:

record 1: 2,5,8,9
record 2: 3,5,6
record 3: 2,4,7,9
record 4: 1,5

it should return records 1,2,4 -- and then, based on the foreign key m.PersonsID_FK in those records, it should grab ithe appropriate data from the other two tables. But for some reason when I include the specialty criterion it returns 0 records. The data is in the tables, I'm just not writing the query correctly to get it but I don't see what I'm doing wrong.

I'm using MySQL. Is there something like Coldfusion's CONTAINS operator I could use? Then I could try reversing the order of the elements in the WHERE clause (though it shouldn't make a difference):

WHERE p.City = '#session.city#' and m.Specialties CONTAINS #session.specialty#


0
 
bwasyliukCommented:
A couple of things I would recommend:

1. Put a <cfouput> around your query and then take the text produced and run it in a query window (SQL Query Analyzer if using MSSQL 2000).
2. put this code inside a stored procedure in the database (assuming its not msaccess) and then you can call it with your parameters:

<cfquery name="getmemberspecialties" datasource="#session.dsn#">
  GetMemberSpecialties_lst '#session.city#','#session.specialty#'
</cfquery>

When you say the query "doesn't work as expected" how are your results different from what you expect?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
bwasyliukCommented:
Looks like I was typing while you posted your last response,

If you run a query like this in MySQL (I dont use it so I am asking) do you get any records returned?

SELECT 1
FROM tbl_members
WHERE 2 IN (Specialties)

If this doesnt return any records, this syntax must not be supported in MySQL.  Let me know if that works - I am curious to know
0
 
brucecrabtreeAuthor Commented:
It does work in MySQL. There is another column in the same table, called Counties, that also contains lists of the same type as the column Specialties. If I run this test query:

select *
from tbl_members
where 35 in (Counties)

I get records returned as I would expect, for all the rows in which the number 35 is included in the list in the column Counties.

But when I run this test query:

select *
from tbl_members
where 5 in (Specialties)

I get no records returned. However there are definitely records in the table with the number 5 in the column Specialties. Both colums are varchar. i can't figure out why one works but not the other.
0
 
brucecrabtreeAuthor Commented:
OK, I figured out that it only returns records when the specified number is the first number in the list. If the specified number is not the first number, no records are returned. So apparently the IN operator does not work with MySQL. Can anyone suggest another way to get the result I'm after?
0
 
brucecrabtreeAuthor Commented:
Found the solution. This works in MySQL:

 select *
 from tbl_members
 where LOCATE(5, Specialties)
0
 
brucecrabtreeAuthor Commented:
This also works:

select *
from tbl_members
where Specialties LIKE '%5%'

0
 
bwasyliukCommented:
Your last post might be dangerous though, because it would match all of the following lists:

1,5,10

1,25,30

12,50,20

I know your post above says your values are 1-10, but this is still a concern, because '%1%' will match when 1 or 10 are present, which is not accurate.

It appears that you found the solution with "LOCATE(5, Specialties)", good job.  I use MSSQL a lot - so I am not familiar with that function.
0
 
brucecrabtreeAuthor Commented:
Yes, thanks, I found that out too after I made my last post. I'm testing more now to be sure the same problem doesnt' happen with LOCATE. I'll post again tomorrow once I've determined for sure that it works. Thanks a lot for your help.
0
 
brucecrabtreeAuthor Commented:
The same problem occurs with LOCATE. Since there doesn't seem to be an SQL function that recognizes items between delimiters as discrete list items, I'll have to append and prepend a comma on every list and then include the commas in the search, like this:

where LOCATE(',5,', Specialties)

that takes care of it
0
 
PAQ_ManCommented:
Closed, 500 points refunded.
PAQ_Man
Community Support Moderator
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now