21. januar 2004 - 11:45Der er
15 kommentarer og 1 løsning
SQL joine 3 tabeller med flere kriterier for samme colum
Hej experts
Sidder lige med en database hvor jeg skal joine 3 tabeller: Tabel Experts Tabel Language Tabel KeySkills
Selve joinen er rimgelig nem at lave - desværre er der selvfølgelig flere krav.
Enkelte gange er der nemlig brug for at søgningen skal retunere de eksperter der har flere specifikke KeySkills - dvs. eksempelvis Project Management og Business Development.
Har selv lavet følgende SQL sætning:
SELECT Distinct Experts.ExpertID, Experts.Title, Experts.FirstName, Experts.LastName, Experts.Email, Experts.FileNameExpert1, Experts.FileNameExpert2 FROM ((Experts INNER JOIN [Language] ON Experts.ExpertID = [Language].ExpertID) INNER JOIN KeySkill ON Experts.ExpertID = KeySkill.ExpertID) INNER JOIN SpecificSkill ON Experts.ExpertID = SpecificSkill.ExpertID WHERE (Keyskill.KeySkill='Human Resources Development' AND Keyskill.KeySkill='Project Management') ORDER BY Experts.Email
Problemet er at søgningen ikke retunere nogle eksperter nu - omend eksempelvis ekspertID 1 både er oprettet med KeySkill Project Management og Human Resources Development. Problement skyldes selvfølgelig at søgningen prøver at finde ExpertID der i samme række opfylder ovenstående (og de findes jo ikke da hver KeySkill får sin egen række med ExpertID og KeySkill) - tror jeg nok.
Men hvordan laver jeg så en søgning der kan tilfredsstille ovenstående?
Den moderne arbejdsplads er i stigende grad afhængig af mødelokaler til at fremme samarbejde, men dette skift medfører også stigende sikkerhedsudfordringer.
Eftersom du lader til at have en kolonne for key-skill bliver du noed til at finde frem til de to key-skills separat og derefter finde ekspert-ID som eksisterer i begge datasaet.
You dont select any fields from Language so you dont need this in your SQL and as far as I see it toy dont select fields from Keyskills. And as your fourth table (SpecificSkill) has the same foreign key as KeySkills then you could just as well make a join between experts and KeySkills. Then what is more important is that you MUST change AND to OR
The fourth table has been included because it was meant to be used in the search as well. Guess i can just delete that one:)
Regarding the table Language, the search is supposed to be dynamic, so sometimes Language will be included in the search like KeySkill.
Maybe i have misunderstood, but using OR would return every person with either KeySkill=Project management or Human R. Development - i just need the persons who have both skills - so OR is not acceptable i guess.
OK if they must have BOTH skills the you will need to GROUP on Experts.ExpertID. Then use OR to select records WHERE (Keyskill.KeySkill='Human Resources Development' OR Keyskill.KeySkill='Project Management'). Then you need to have a column to count the number of records returned within the GROUP (Experts.ExpertID) an dthen set this criteria to = 2 (to make sure that BOTH 'Human Resources Development' AND 'Project Management' where included in the count.
f you use AND in the WHERE then the column Keyskill.KeySkill (for a single record) has to be BOTH 'Human Resources Development' AND 'Project Management' and that isnt logical is it :o)
No it isent - but as i wrote earlier, that was the problem:).
Anyways - i have found the solution - ofcause you just have to use exists
Select Experts.ExpertID From Experts Where exists (Select * From KeySkill Where KeySkill.KeySkill = 'Energy and Environment' AND Experts.ExpertID=KeySkill.ExpertID AND exists (SELECT * FROM KeySkill Where KeySkill.KeySkill = 'Human Resources Development' AND Experts.ExpertID=KeySkill.ExpertID));
it will be some fun to make that dynamic i guess;(
thx for the help, or should i say facilitating me to make my own help :)
Oh sorry terry - just missed your solution with is far better compared to the changes i have to make - please drop an answer and the points shall be yours for the taken :)
when i trie to use your solution, i keep getting the same error:
SELECT Experts.ExpertID, Experts.FirstName, Experts.LastName, Experts.Email, Experts.FileNameExpert1, Experts.FileNameExpert2 FROM Experts GROUP BY Experts.ExpertID HAVING COUNT(Experts.ExpertID) > 1 Microsoft JET Database Engine error '80040e21'
You tried to execute a query that does not include the specified expression 'FirstName' as part of an aggregate function.
Do you have any idea about what to do to correct it?
You have to GROUP on ALL fields which you select other than any which you use aggregate functions on (SUM, COUNT etc.) If you make your query in Access' query builder it will be MUCH easier!
I can also do it for you if you send me your dB, but it will first be when I get home after work!§
Unfortunately i have just realised that the count solution might involve some problems, since the search is supposed to handle free text searches as well.
I'll give my other solution a go.
If that dosent work, you can count on that i will email you. Such an offer one shouldnt say no to:). Thx for your help so far - always great to meet people that are willing to help.
If you do send me the dB then please give a referenc eto this question so I know what I am receiving. Also an short explanation as to what you want would be nice too :o)
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
