Menu
its Access database..
i have a Library table, where Autnm Topic Size Cover Lang are foreign keys
each record is actually a book which has its properties such as author and stuff. i am not quite sure i am even using the correct JOIN.. quite new with "complex" SQL :)
SELECT Library.Bknm_Hebrew, Library.Bknm_English, Library.Bknm_Russian, Library.Note,
Library.ISBN, Library.Pages, Library.PUSD, Author.ID AS [AuthorID],
Author.Author_hebrew AS [AuthorHebrew],
Author.Author_English AS [AuthorEnglish],
Author.Author_Russian AS [AuthorRussian], Topic.ID AS [TopicID],
Topic.Topic_Hebrew AS [TopicHebrew], Topic.Topic_English AS [TopicEnglish],
Topic.Topic_Russian AS [TopicRussian], Size.Size AS [Size],
Cover.ID AS [CoverID], Cover.Cvrtyp_Hebrew AS [CoverHebrew],
Cover.Cvrtyp_English AS [CoverEnglish], Cover.Cvrtyp_Russian AS [CoverRussian],
Lang.ID AS [LangID], Lang.Lang_Hebrew AS [LangHebrew],
Lang.Lang_English AS [LangEnglish],
FROM Library INNER JOIN Author ON Library.Autnm = Author.ID
INNER JOIN Topic ON Library.Topic = Topic.ID
INNER JOIN Size ON Library.Size = Size.ID
INNER JOIN Cover ON Library.Cover = Cover.ID
INNER JOIN Lang ON Library.Lang = Lang.ID
WHERE (TopicID=13 AND LangID=1) ORDER BY LangID ASC
Edit: After inserting the parantheses @Guffa suggested, I got a new error:
Too few parameters. Expected 3.
359
The syntax for multiple joins: SELECT column_name1,column_name2,.. FROM table_name1 INNER JOIN table_name2 ON condition_1 INNER JOIN table_name3 ON condition_2 INNER JOIN table_name4 ON condition_3 . . . Note: While selecting only particular columns use table_name.
The most common way of joining three tables goes something like this: SELECT * FROM Table1 INNER JOIN Table2 ON Condition INNER JOIN Table3 ON Condition; This uses an inner join, but you can specify your desired join type as with any other join. You can also combine join types if required (example below).
In Access you need parentheses if you have more than one join. Also, as Ivar pointed out, you have an extra comma after the last item in the field list.
select
Library.Bknm_Hebrew, Library.Bknm_English, Library.Bknm_Russian, Library.Note,
Library.ISBN, Library.Pages, Library.PUSD, Author.ID as [AuthorID],
Author.Author_hebrew as [AuthorHebrew], Author.Author_English as [AuthorEnglish],
Author.Author_Russian as [AuthorRussian], Topic.ID as [TopicID],
Topic.Topic_Hebrew as [TopicHebrew], Topic.Topic_English as [TopicEnglish],
Topic.Topic_Russian as [TopicRussian], Size.Size as [Size], Cover.ID as [CoverID],
Cover.Cvrtyp_Hebrew as [CoverHebrew], Cover.Cvrtyp_English as [CoverEnglish],
Cover.Cvrtyp_Russian as [CoverRussian], Lang.ID as [LangID],
Lang.Lang_Hebrew as [LangHebrew], Lang.Lang_English as [LangEnglish]
from
(((((Library
inner join Author on Library.Autnm = Author.ID)
inner join Topic on Library.Topic = Topic.ID)
inner join Size on Library.Size = Size.ID)
inner join Cover on Library.Cover = Cover.ID)
inner join Lang on Library.Lang = Lang.ID)
The error message "Too few parameters." means that you have defined parameters in the query that is not sent along when it's executed. As you don't use any parameters in the query it means that you have spelled some names in the query wrong, so that it thinks that they are parameters instead.
186
I'm not too familiar with Access, and therefore not sure if it accepts this, but I would start by removing the last comma from the select list.
Lang.Lang_English AS [LangEnglish], => Lang.Lang_English AS [LangEnglish]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
