Hey.
This problem is only a problem because your
TBL_SPECIALITY.Specialty_Tags field violates the first rule of relational databases: Each field should only contain a single piece of data.
(See this article for a more detailed explanation.)
MySQL has no way of knowing that the string in that field is meant to be a list of keywords, so none of the typical search methods can be applied to it. You could try to manually search the string, but that's usually a performance killer.
What you should do, is remove the field entirely and set up a table to store a list of available tags, and a table that links tags to specialties.
- tbl_speciality
-
specialty_id (PK)
-
specialty_name (eg. family law)
-
- tbl_tag
-
tag_id (PK)
-
tag_name (eg. marriage, divorce, custody)
-
- tbl_speciality_tag
-
specialty_id (PK, FK)
-
tag_id (PK, FK)
And then you could do a query like:
- SELECT
-
p.practice_name,
-
b.branch_suburb,
-
b.branch_postcode
-
FROM tbl_practice AS p
-
INNER JOIN tbl_branch AS b
-
ON p.practice_id = b.Practice_id
-
INNER JOIN tbl_branch_speciality_map AS sm
-
ON sm.branch_id = b.branch_id
-
LEFT JOIN tbl_speciality AS s
-
ON sm.speciality_id = s.speciality_id
-
WHERE
-
p.practice_name LIKE '% PARAMETER_PRACTICE %'
-
AND( b.suburb_name LIKE '% PARAMETER_LOCATION %'
-
OR b.suburb_postcode = ' PARAMETER_LOCATION ')
-
AND
-
s.speciality_id IN(
-
SELECT st.speciality_id
-
FROM tbl_speciality_tag AS st
-
INNER JOIN tbl_tag AS t
-
ON t.tag_id = st.tag_id
-
WHERE
-
t.tag_name IN('Tag1', 'Tag2')
-
)
Note, the query is just an example. There might be a bug or two in there I can't spot atm.
Also,
I recommend you reconsider your naming conventions.
The style I used in my examples, all lower-case using "_" as a word-separator, is a very good one.
Generally, in SQL databases, it is best to reserve all upper-case words for SQL commands, and use all lower-case letters in table and field names. It makes it easier to distinguish between SQL syntax and names, and avoids the case-confusion created by Windows based servers.
(Windows is case-insensitive, while Unix based systems are case-sensitive, so Windows doesn't realize if you use the wrong case, while Unix does.)
Ultimately, it is most important to be consistent, to choose a style and use it exclusively throughout the project.
