SELECT From a field separated by a comma.

[new2sql]

Hi there, I am not sure if this can be done entirely in SQL. Anyhow, I have the following 4 tables.

TBL_Practice
Practice_ID (PK)
Practice_Name
etc.

TBL_Branch
Branch_ID (PK)
Practice_ID (FK)
Branch_Suburb
Branch_Postcode
etc.

Each branch has multiple specialties that they can practice in

TBL_SPECIALTY
Specialty_ID (PK)
Specialty_Name (eg. family law)
Specialty_Tags (eg. contains a list of tags: marriage, divorce, custody)

Then there is a mapping table as such.

TBL_BRANCH_SPECIALTY_MAP
Branch_ID (FK)
Specialty_ID(FK)

Thats the database section, then on the frontend I then have a search engine with 3 textboxes.

[Practice Name or Part thereof]
[Practice Suburb or Postcode]
[Keywords]

If someone enters a keyword that appears in TBL_SPECIALTY.Specialty_Tags it should bring up a matching practice.

I have the first two boxes worked out but cannot figure out how to get the keywords working. (or if it can be done).

My SQL so far is:

SELECT  TBL_PRACTICE.Practice_Name, TBL_BRANCH.Branch_Suburb, TBL_BRANCH.Branch_Postcode
FROM TBL_PRACTICE INNER JOIN
TBL_BRANCH ON TBL_PRACTICE.Practice_ID = TBL_BRANCH.Practice_ID 
WHERE     
TBL_PRACTICE.Practice_Name LIKE '% PARAMETER_PRACTICE %' AND (Suburb_Name LIKE '% PARAMETER_LOCATION %' OR Suburb_Postcode = ' PARAMETER_LOCATION ') 
 

Thanks in advance for any assistance, much appriciated.

[Atli]

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.

[new2sql]

Atli,

Thanks for your advice on this, I had iniitally thought that another table would be the way to go but was not 100% sure. After reading your post I will implement the design as per your recommendations and the recommendations in the normalisation link you attached.

Much appriciated.