469,090 Members | 1,314 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

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.

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:

Expand|Select|Wrap|Line Numbers
  1. SELECT  TBL_PRACTICE.Practice_Name, TBL_BRANCH.Branch_Suburb, TBL_BRANCH.Branch_Postcode
  2. FROM TBL_PRACTICE INNER JOIN
  3. TBL_BRANCH ON TBL_PRACTICE.Practice_ID = TBL_BRANCH.Practice_ID 
  4. WHERE     
  5. TBL_PRACTICE.Practice_Name LIKE '% PARAMETER_PRACTICE %' AND (Suburb_Name LIKE '% PARAMETER_LOCATION %' OR Suburb_Postcode = ' PARAMETER_LOCATION ') 
  6.  
Thanks in advance for any assistance, much appriciated.
Oct 29 '09 #1
2 2903
Atli
5,058 Expert 4TB
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.
Expand|Select|Wrap|Line Numbers
  1. tbl_speciality
  2. specialty_id (PK)
  3. specialty_name (eg. family law)
  4.  
  5. tbl_tag
  6. tag_id (PK)
  7. tag_name (eg. marriage, divorce, custody)
  8.  
  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
  1. SELECT
  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.

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.
Oct 31 '09 #2
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.
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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.