MS Access – Exclude Records with More Than Requested Values in M:N Relationship Query

ms accessms-access-2016subquery

I have a little problem when looking up records that have m:n relationships to other tables and therefore appear multiple times in link tables. I try to show it with a minimal example here:

Let's assume we have a table called "books", one called "authors" and a link table connecting both.

Some books are written by one author, others are written by multiple authors.

As we can see, book one (book_id = 1) is written by only one person (authorID = 1) while book two (book_id = 2) is written by two people (authorID 1 and 3).
If we now try to get the book which is written by author 1 and author 3 we can go like this:

SELECT book_id, title, year_of_publication, pagecount, cover FROM (book
INNER JOIN 
(SELECT * FROM link_author_book WHERE author = 1) AS author1 
ON author1.book = book.book_id)
INNER JOIN
(SELECT * FROM link_author_book WHERE author = 3) AS author2
ON author2.book = book.book_id

which leads to this:

I am pretty sure that my solution to this is not the best way to do it but in this case it works… anyway, when we try to get only those books returned that are written by author 1 and nobody else, we have a problem. In code, this selection looks like this:

SELECT book_id, title, year_of_publication, pagecount, cover FROM book
INNER JOIN 
link_author_book ON link_author_book.book = book.book_id
WHERE author = 1;

and leads to this recordset:

while it is technically true that authorID = 1 is the author of both of these books, what I really wanted to get was only book one (book_id = 1). So here is my question: How can I exclude records that have more than the queried values linked to them in this kind of m:n relationship? And how can this be done as a SQL statement in Access 2016? Thanks in advance

Best Answer

The key is learning to write queries which directly answer the questions. Start with getting books with only one author ("and nobody else"). This will require an aggregate query which counts the authors per book:

SELECT lab.book FROM link_author_book AS lab
GROUP BY lab.book
HAVING Count(lab.author) = 1

Now join the results to the other tables to get detail you desire/require. I personally prefer saving such intermediate queries then referencing them by name in the next query (since you can then use the Access query designer to edit them individually), but to be explicit I'll include it as a subquery:

SELECT book.title, author.lastname
  ((SELECT lab.book, first(lab.author) AS author
    FROM link_author_book AS lab
    GROUP BY lab.book
    HAVING Count(lab.author) = 1)
  AS SingleAuthor
  INNER JOIN book ON SingleAuthor.book = book.book_id)
  INNER JOIN author ON SingleAuthor.author = author.auth_id

Really, you could also write a query which answers your other questions like getting records with "more than the queried values"...

SELECT lab.book FROM link_author_book AS lab
GROUP BY lab.book
HAVING Count(lab.author) <> 1

and then "exclude" such records:

SELECT book.title, author.lastname
  ((book
  LEFT JOIN (SELECT lab.book FROM link_author_book AS lab
  GROUP BY lab.book
  HAVING Count(lab.author) <> 1)
  AS OtherAuthors
  ON book.book_id = OtherAuthors.book)
  INNER JOIN link_author_book AS lab ON book.book_id = lab.book)
  INNER JOIN author ON author.auth_id = lab.author
WHERE OtherAuthors.book Is NULL

This will initially select all records in [books] due to the left join, but will subsequently exclude books with more than 1 author by keeping only records which are NOT in the [OtherAuthors] subquery. But as you see this is an overly complex way to get the same results. I only included this to show that it is possible to exclude records using other types of joins and appropriate conditions. This is a useful technique especially when there is no way of selecting the desired records directly. (Sometimes excluding records really is the best approach, but it just isn't necessary in this case with the link table.)

Related Solutions

Ms-access – Unable to add records to query in Access 2007

Microsoft Access has a setting in the Query Properties window called Recordset Type. You need to set this to Dynaset (Inconsistent Updates) to allow your query to be updatable.

enter image description here

Ms-access – Splitting 1 record on one table to become two records on another table

See if this work.

 CREATE FUNCTION [dbo].[FN_CommaSeparator] (@array VARCHAR(max))

> RETURNS @Table TABLE (value VARCHAR(max))

AS
BEGIN
    DECLARE @separator_position INT
        ,@array_value VARCHAR(max)

    SET @array = @array + ','

    WHILE PATINDEX('%,%', @array) <> 0
    BEGIN
        SELECT @separator_position = PATINDEX('%,%', @array)

        SELECT @array_value = LEFT(@array, @separator_position - 1)

        INSERT @Table
        VALUES (@array_value)

        SELECT @array = STUFF(@array, 1, @separator_position, '')
    END

    RETURN
END

CREATE TABLE #Temp (
    Id VARCHAR(10)
    ,Author VARCHAR(50)
    )

CREATE TABLE #Temp2 (
    Id VARCHAR(10)
    ,Author VARCHAR(50)
    )

INSERT INTO dbo.#Temp (
    Id
    ,Author
    )
VALUES (
    'S1'
    ,'ABC,BCD'
    )
    ,(
    'S2'
    ,'CDE,DEF'
    )
    ,(
    'S3'
    ,'EFG,FGH'
    )
    ,(
    'S4'
    ,'GHI,HIJ'
    )

DECLARE @Id VARCHAR(50)
    ,@Author VARCHAR(50)

DECLARE ABC CURSOR
FOR
SELECT Id
    ,Author
FROM dbo.#Temp t

OPEN ABC

FETCH NEXT
FROM ABC
INTO @Id
    ,@Author

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO dbo.#Temp2 (
        Id
        ,Author
        )
    SELECT @Id
        ,Value
    FROM dbo.FN_CommaSeparator(@Author)

    FETCH NEXT
    FROM ABC
    INTO @Id
        ,@Author
END

DEALLOCATE ABC

SELECT *
FROM dbo.#Temp2 t

DROP TABLE dbo.#Temp

DROP TABLE dbo.#Temp2

Best Answer

Related Solutions

Ms-access – Unable to add records to query in Access 2007

Ms-access – Splitting 1 record on one table to become two records on another table

Related Question