Complex search Query

[win82]

Tables are in bold

 

user

 

id  email password

 

 

user_details

 

id user_id  name  city  state  zip

 

 

 

speciality

 

id  user_id  speciality_info_id

 

 

 

 

speciality_info

 

id  speciality_info

 

1    Web Developer

2    Web Designer

3    Web Tutor

 

 

languages

 

id user_id  language_info_id

 

 

 

language_info

 

id language_info

 

1  PHP

2  PERL

3  COLD FUSION

 

 

 

 

The above are the tables. I need to write the search query but I don't know how to ..

 

 

All 'user' has a record in 'user_details' table. One user may have more than one 'speciality'. But sometimes, user won't select any 'speciality'. Same like speciality,

 

user may have more than record in 'languages' and sometimes won't have any records.

I need to do the search

with:

 

City, State, Zip, language name  (like just type 'P', then list all users who are selected languages with starting letter 'P') and speciality ( type speciality name in

 

the speciality_info).

 

I have started:

 

 

SELECT

user.email,

user_details.name,

user_details.city,

user_details.state

 

FROM

user

 

INNER JOIN user.id ON user_details.user_id

 

Please help me to join with specility and language tables.

 

 

Thanks in advance.

 

[artacus]

You need to use a left join when joining languages and specialty or you wont get any records that don't have a specialty and language.

[win82]

 

SELECT

  user.email,

  user_details.name,

  user_details.city,

  user_details.state

 

FROM

  user

INNER JOIN user_details  ON  user.id = user_details.user_id

LEFT  JOIN speciality      ON  user.id = speciality.user_id

 

please guide me to search for the speciality_info ( a letter "P")? and join speciality and speciality_info

[kickstart]

Hi

 

Do you want to retrieve info where a person doesn't have (say) a language selected? If will you effectively ignore people who have no langauge selected?

 

If you only want those which match on both:-

 

SELECT 
   user.email,
   user_details.name,
   user_details.city,
   user_details.state
FROM user
INNER JOIN user_details ON user.id = user_details.user_id 
INNER JOIN speciality ON user.id = speciality.user_id
INNER JOIN speciality_info ON speciality.speciality_info_id = speciality_info.id
INNER JOIN languages ON user.id = languages.user_id
INNER JOIN languages_info ON languages.languages_info_id = languages_info.id
WHERE language_info LIKE 'P%'
AND speciality_info = 'SomeEnteredSpeciality'

 

All the best

 

Keith