Skip to content

Instantly share code, notes, and snippets.

@hrach
Last active August 29, 2015 14:02
Show Gist options
  • Save hrach/a814eb6e715080fa781a to your computer and use it in GitHub Desktop.
Save hrach/a814eb6e715080fa781a to your computer and use it in GitHub Desktop.
@hrachcz: #anketa. model: author 1:n books m:n tags. zajima me sql, ktere vybere autory, kteri nenapsali zadnou knihu o PHP (tj. bez tagu PHP).
select *
from authors
where not exists (
select *
from books
left join books_x_tags on (books_x_tags.book_id = books.id)
left join tags on (tags.id = books_x_tags.tag_id)
where
books.author_id = authors.id
and tags.name = 'PHP'
)
@milo
Copy link

milo commented Jun 17, 2014

@ZZromanZZ To je špatně. Vypadnou Ti autoři bez knih (sporné zadání) a dostaneš i autora, který napsal více knih včetně jedné o PHP.

@mrfrostikcz
Copy link

@milo 👍

@ZZromanZZ
Copy link

Pravda, netestuju vysledek.

Co tohle ?

SELECT * FROM author WHERE authorID IN (SELECT authorID FROM book WHERE bookID NOT IN (SELECT bookID FROM tag_x_book WHERE tagID = 'PHP'))

Ale uz si nemyslim(pokud je to dobre), ze to bude nejak vyrazne rychle...

@hrach
Copy link
Author

hrach commented Jun 17, 2014

@milo no jasny :) 👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment