CODEWITHSUNDEEP
sqlsqlite
Aaron M
Aaron M

Reputation: 391

Many-to-many query selecting records with no match from the right as well as values from the left in SQLite

I have a standard many-to-many schema like this:

items table:

id name
1 foo

groups table:

id name slug
1 baz qux

items_to_groups:

item_id group_id
1 1

The groups.slug is used to query Group information. The ID is internal.

I need to query for items that are not in a specified group, but also need to include the group attributes (like name) in the result set.

The query is trivial without the need for values from the groups table, but I cannot figure out how to include them.

Here is my best attempt:

SELECT
    g.slug,
    g.name,
    i.name
FROM
    items AS i
    LEFT JOIN items_to_groups AS itg ON i.id = itg.item_id
    LEFT JOIN groups AS g ON itg.group_id = g.id
    AND g.slug = 'group-slug-1'
WHERE
    itg.item_id IS NULL;

Results:

||Item name 1
||Item name 2

Desired results:

group-slug-1|1|Item name 1
group-slug-1|1|Item name 2

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270201

Hmmm . . . I'm thinking:

select i.*, g.*
from items i cross join
     groups g
where g.slug = 'group-slug-1' and
      not exists (select 1
                  from items_to_groups ig
                  where ig.item_id = i.id and ig.group_id = g.id
                 );

Upvotes: 1

Related Questions