brucecrabtree
asked on
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.
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.
ASKER
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#
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#
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
GetMemberSpecialties_lst '#session.city#','#session
</cfquery>
When you say the query "doesn't work as expected" how are your results different from what you expect?
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
GetMemberSpecialties_lst '#session.city#','#session
</cfquery>
When you say the query "doesn't work as expected" how are your results different from what you expect?
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
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
ASKER
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.
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.
ASKER
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?
ASKER
Found the solution. This works in MySQL:
select *
from tbl_members
where LOCATE(5, Specialties)
select *
from tbl_members
where LOCATE(5, Specialties)
ASKER
This also works:
select *
from tbl_members
where Specialties LIKE '%5%'
select *
from tbl_members
where Specialties LIKE '%5%'
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.
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.
ASKER
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.
ASKER
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
where LOCATE(',5,', Specialties)
that takes care of it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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#)