472,111 Members | 1,895 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,111 software developers and data experts.

SELECT From a field separated by a comma.

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

Practice_ID (PK)

Branch_ID (PK)
Practice_ID (FK)

Each branch has multiple specialties that they can practice in

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.

Branch_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]

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:

Expand|Select|Wrap|Line Numbers
  1. SELECT  TBL_PRACTICE.Practice_Name, TBL_BRANCH.Branch_Suburb, TBL_BRANCH.Branch_Postcode
  4. WHERE     
Thanks in advance for any assistance, much appriciated.
Oct 29 '09 #1
2 2966
5,058 Expert 4TB

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.
Expand|Select|Wrap|Line Numbers
  1. tbl_speciality
  2. specialty_id (PK)
  3. specialty_name (eg. family law)
  5. tbl_tag
  6. tag_id (PK)
  7. tag_name (eg. marriage, divorce, custody)
  9. tbl_speciality_tag
  10. specialty_id (PK, FK)
  11. tag_id (PK, FK)
And then you could do a query like:
Expand|Select|Wrap|Line Numbers
  2.     p.practice_name, 
  3.     b.branch_suburb, 
  4.     b.branch_postcode
  5. FROM tbl_practice AS p
  6. INNER JOIN tbl_branch AS b
  7.     ON p.practice_id = b.Practice_id
  8. INNER JOIN tbl_branch_speciality_map AS sm
  9.     ON sm.branch_id = b.branch_id
  10. LEFT JOIN tbl_speciality AS s
  11.     ON sm.speciality_id = s.speciality_id
  12. WHERE     
  13.     p.practice_name LIKE '% PARAMETER_PRACTICE %' 
  14. AND(    b.suburb_name LIKE '% PARAMETER_LOCATION %' 
  15.     OR  b.suburb_postcode = ' PARAMETER_LOCATION ') 
  16. AND
  17.     s.speciality_id IN(
  18.         SELECT st.speciality_id
  19.         FROM tbl_speciality_tag AS st
  20.         INNER JOIN tbl_tag AS t
  21.             ON t.tag_id = st.tag_id
  22.         WHERE 
  23.             t.tag_name IN('Tag1', 'Tag2')
  24.     )
Note, the query is just an example. There might be a bug or two in there I can't spot atm.

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.
Oct 31 '09 #2

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.
Nov 1 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Greg Bryant | last post: by
3 posts views Thread by Funnyweb | last post: by
4 posts views Thread by Mick White | last post: by
1 post views Thread by Mark | last post: by
22 posts views Thread by MP | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.