Help with Delphi 7, ADO, & MS Access SQL Statement

Question

I have three tables (in a MS Access Database 2000 file *.mdb)

Knowledge
id
question
answer

Knowledge_Keywords
id
knowledgeid
keywordsid

Keywords
id
keyword

Need to get all the keywords for a knowledge

Select distinct keyword from keywords KW
Join knowledge_keywords KKW on KKW.keywordid = KW.id
Join Knowledge K on K.id = KKW.knowledgeid
Where k.id = 10

of course 10 is a example, i actually use a parameter there

Where k.id = :AKnowId';

and fill it in in code

qry.Parameters.ParamByName('AKnowId').Value:= AKnowledgeId;

anyway, i think the SQL is qrong, any help would be greatly appreciated

Testing in delphi, it crashes in my try except when it reaches the query's Open. Testing it in MS Access, i get error in From clause. Adding parenthesis and retesting it in MS ACCESS, i get syntax error in join operation. — IElite
– IElite, Commented Dec 14, 2010 at 19:12

Chris Thornton · Accepted Answer · 2010-12-14 19:30:12Z

1

Get the SQL working properly within Access itself (make a query, try your SQL, see if it returns anything). THEN worry about Delphi.

answered Dec 14, 2010 at 19:30

Chris Thornton

15.9k5 gold badges39 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

IElite Over a year ago

Yes, thank you, as i stated in my comment above, i did try to get it to work in MS ACCESS, but failed as well.

David-W-Fenton Over a year ago

You failed in Access? Were you using the QBE to write the SQL? If not, why not? It guarantees Access-compatible SQL, so it's a great way to get it working. And it saves a boatload of typing.

IElite · Accepted Answer · 2010-12-14 19:33:14Z

1

Solved it!

Select distinct keyword
from (keywords KW
inner Join knowledge_keywords KKW on KKW.keywordid = KW.id)
inner Join Knowledge K on K.id = KKW.knowledgeid
Where k.id = 10

answered Dec 14, 2010 at 19:33

IElite

1,8489 gold badges42 silver badges65 bronze badges

1 Comment

IElite Over a year ago

I guess MS ACCESS in particular about using parenthesis and i guess i needed inner joins verses plain ol joins <smile>

Collectives™ on Stack Overflow

Help with Delphi 7, ADO, & MS Access SQL Statement

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related