SELECT problem

Min søgning bliver genereret i en Java-applikation, som sender statementet til MySQL databasen.
Søgningen er fonetisk, og gør brug af 8 tabeller. Det meste af søgningen er allerede lavet.

Den mangler at kunne hente 'Shopname', idet den finder medarbejdere der passer til søgningen. Problemet er at ikke alle medarbejdere har fået angivet et shopid, eller måske endda fået angivet et forkert shopid hvortil der ingen butik findes. Derfor skal søgningen kun hente shopnavnet, hvis der findes et shopnavn til medarbejderens shopid.


Sådan ser søgningen ud, når man søger på fornavnet Hans:
--------------------------------------------------------
SELECT e.employeeid,
IF(ex.beocademyno Is Not Null, ex.jobtitle, e.jobtitle) jobtitle,
IF(ex.beocademyno Is Not Null, ex.firstname, e.firstname) firstname,
IF(ex.beocademyno Is Not Null, ex.lastname, e.lastname) lastname,
IF(ex.beocademyno Is Not Null, ex.address1, e.address1) address1,
IF(ex.beocademyno Is Not Null, ex.address2, e.address2) address2,
IF(ex.beocademyno Is Not Null, ex.zipcode, e.zipcode) zipcode,
IF(ex.beocademyno Is Not Null, ex.city, e.city) city,
IF(ex.beocademyno Is Not Null, ex.countryid, e.countryid) countryid,
IF(ex.beocademyno Is Not Null, ex.phone, e.phone) phone,
IF(ex.beocademyno Is Not Null, ex.mobile, e.mobile) mobile,
IF(ex.beocademyno Is Not Null, ex.fax, e.fax) fax,
IF(ex.beocademyno Is Not Null, ex.email, e.email) email,
IF(ex.beocademyno Is Not Null, ex.birthday, e.birthday) birthday, e.hiredsince,
IF(ex.beocademyno Is Not Null, ex.sex, e.sex) sex,
IF(ex.beocademyno Is Not Null, ex.nativelanguage, e.nativelanguage) nativelanguage,
IF(ex.beocademyno Is Not Null, ex.otherlanguage, e.otherlanguage) otherlanguage,
IF(ex.beocademyno Is Not Null, ex.region, e.region) region, e.careeraspiration, e.cv, e.employeeno, e.beocademyno,
IF(ex.beocademyno Is Not Null, ex.cardno, e.cardno) cardno,
IF(ex.beocademyno Is Not Null, ex.cardstatus, e.cardstatus) cardstatus,
IF(ex.beocademyno Is Not Null, ex.active, e.active) active, e.salesperson, e.technician, e.skillsinaudio, e.skillsinvideo, e.skillsininstallation, e.trainer, e.shopowner, e.leader, e.internalemployee, e.username, e.password, e.access, e.shopid
FROM Employee e  LEFT JOIN ExternEmployee ex ON e.beocademyno=ex.beocademyno WHERE
((STRCMP(SOUNDEX(e.firstname), SOUNDEX('Hans')) = 0 OR e.firstname LIKE '%Hans%') OR (STRCMP(SOUNDEX(ex.firstname), SOUNDEX('Hans')) = 0
OR ex.firstname LIKE '%Hans%')) ORDER BY e.firstname, e.lastname



Tabellerne:
-----------
Employee:
---------
employeeid, jobtitle, firstname, lastname, address1, address2, zipcode, city, countryid, phone, mobile, fax, email, birthday, hiredsince, sex, nativelanguage, otherlanguage, region, careeraspiration, cv, employeeno, beocademyno, cardno, cardstatus, active, salesperson, technician, skillsinaudio, skillsinvideo, skillsininstallation, trainer, shopowner, leader, internalemployee, username, password, access, shopid

employeeid er primærnøgle
countryid er fremmednølge
beocademyno er fremmednølge
shopid er fremmednølge


ExternEmployee:
---------------
beocademyno, jobtitle, firstname, lastname, address1, address2, zipcode, city, country, state, cardno, cardstatus, active, phone, mobile, fax, email, birthday, sex, nativelanguage, otherlanguage, region

beocademyno er primærnøgle


Shop:
-----
shopid, shoptypeid, regionid, externshopno

shopid er primærnøgle
resten er fremmednøgler


ExternShop:
-----------
externshopno, name, address1, address2, zipcode, city, country, state, phone, mobile, fax, email, segment, region, type

externshopno er primærnøgle


Desværre kan jeg ikke indsætte et E/R-diagram er, men hvis du gerne vil se det, vil jeg meget gerne maile det.