Solved Nested Query (1 Viewer)

[Romio_1968]

Hello,

I have the following tables about a book library

One title can have many authors. One author can write many titles. A Many-To-Many relationship is set using TAJunction tables. This table is used to achieve the relationship between table "Titles" and "Authors", using joins betwen Title_ID-Title_IDFK and Author_ID-Author_IDFK.
Also, title may not have an author (case of collective authors such Magazines, or documents issued by authorities, situation when we have only editors or publishers)

For the inventory si more simple: A one-to-one relationship. There is no title without Inventory_No. A title may have many Inventory_No, corresponding to the copies it may have.

I tried to build a query that shows for each Title_ID and the Inventory numbers and the authors. (Basicaly all the info inside the 4 tables, to be then filtered)

For testing, I added 2 Titles. Two copies (Inventory_ID) of one title with 3 authors and one copy of a title with no authors.
But the last one is a no show. Is missing from query results.

The SQL code behind the query is:

SELECT Titles.Title_ID, Titles.Timestamp, Titles.Title, Titles.Publisher, Titles.PublishPlace, Titles.PrintYear, Titles.Media, Titles.ClassCode1, Titles.ClassCode2, Titles.ClassCode3, Author.Author_Name, Inventory.Inventory_No
FROM (Titles
INNER JOIN (Author
INNER JOIN TAJunction
ON Author.Author_ID = TAJunction.Author_IDFK)
ON Titles.Title_ID = TAJunction.Title_IDFK)
INNER JOIN Inventory
ON Titles.Title_ID = Inventory.Title_IDFK
ORDER BY Titles.Title_ID, Inventory.Inventory_No;
If you please help me fix this.

For practical reason, yo may skip most of the fields ftom "Titles" table

Thank You.

RESOLUTION
_________________________________________________________________________________________________________________
I fixed the s.o.b.

First issue was in the relationships

And now the code looks like this:

SELECT Titles.Title_ID, Titles.Timestamp, Titles.Title, Titles.Publisher, Titles.PublishPlace, Titles.PrintYear, 
       Titles.Media, Titles.ClassCode1, Titles.ClassCode2, Titles.ClassCode3, Author.Author_Name, Inventory.Inventory_No
FROM 
    (Titles 
    INNER JOIN Inventory 
        ON Titles.Title_ID = Inventory.Title_IDFK) 
    LEFT JOIN 
        (Author 
        RIGHT JOIN TAJunction 
            ON Author.Author_ID = TAJunction.Author_IDFK) 
        ON Titles.Title_ID = TAJunction.Title_IDFK
ORDER BY Titles.Title_ID, Inventory.Inventory_No;

 

[The_Doc_Man]

I might make a layered query (specifically, a query that calls for data from another query rather than directly from a table.

Your junction for titles vs. authors is quite reasonable. If you have a two-way (rather than three-way) JOIN for author/junction/title, then you can write a second query that does the junction between the inventory ID and the title (from the query). Since queries contain no data unless they are open, it costs you nothing in the way of storage to have the extra fields in both the inner and outer queries.

As to the "title without an author", there is always the idea of having a "fake" author record called "Collection" or "Anthology" or something like that. The other approach would be to have the "inner" query do an INNER join between the junction table and the title, but a LEFT JOIN between the junction table and the author. If you do that, you will have to watch for the presence of nulls, but otherwise you would be OK.

 

[Romio_1968]

I might make a layered query (specifically, a query that calls for data from another query rather than directly from a table.

Your junction for titles vs. authors is quite reasonable. If you have a two-way (rather than three-way) JOIN for author/junction/title, then you can write a second query that does the junction between the inventory ID and the title (from the query). Since queries contain no data unless they are open, it costs you nothing in the way of storage to have the extra fields in both the inner and outer queries.

As to the "title without an author", there is always the idea of having a "fake" author record called "Collection" or "Anthology" or something like that. The other approach would be to have the "inner" query do an INNER join between the junction table and the title, but a LEFT JOIN between the junction table and the author. If you do that, you will have to watch for the presence of nulls, but otherwise you would be OK.

I posted above the solution in the "RESOLUTION" part, with alterations of the initial joins. There was the issue. Still a many to many, yet different.
Thank you

 

[Pat Hartman]

I posted a comment in another of your recent threads pointing out the error in your schema.

Solved - Concatenate a query group. Is it possible?

I have the following table structure and relationships I created a query that extracts data from the above tables. The SQL code for this Query is: SELECT Titles.Title_ID, Titles.Title, Author.Author_Name, Inventory.Inventory_No FROM (Titles INNER JOIN Inventory ON Titles.Title_ID =...

www.access-programmers.co.uk

 

[Romio_1968]

Noted and answered. Thank you.