Need help with a query using lookup table example

Databases
1

Ref. Kevin Yank’s look-up table example BYO Datadriven Website 4thEd, p167 – I had this question immediately, but finished the entire book (and searched this forum) before posting this.

THREE TABLES

joke table:
id | joketext

1 Why did the chicken…
2 knock knock who’s…
3 A man walks into a bar…
4 how many lawyers does…

category table:
id | name

1 Knock-knock
2 Cross the road
3 Lawyers
4 Walk the bar
5 light bulb

CREATE TABLE jokecategory (
jokeid INT NOT NULL,
categoryid INT NOT NULL,
PRIMARY KEY (jokeid, categoryid)
)

jokecategory table:
joke id | category id

1 2
2 1
3 4
4 3
4 5

FOR REFERENCE THIS QUERY WORKS -> List jokes in the “Knock-Knock” OR “Lawyers” Catergories:

SELECT joke.joketext
FROM joke INNER JOIN jokecategory
ON joke.id = jokecategory.jokeid
INNER JOIN category
ON jokecategory.categoryid = category.id
WHERE category.name = “Knock-Knock” OR category.name = “Lawyers”;

*** MY QUESTION: What is the query (or direction to go in) in order to list Jokes in the “Knock-Knock” AND “Lawyers” Catergories ?

for example this does not work:
WHERE category.name = “Knock-Knock” AND category.name = “Lawyers”;

Thanks!

2

you have to count the relationship rows and make sure both of them were present

SELECT joke.joketext
  FROM ( SELECT jokecategory.jokeid
           FROM category
         INNER
           JOIN jokecategory
             ON jokecategory.categoryid = category.id
          WHERE category.name IN ( 'Knock-Knock' , 'Lawyers' )
         GROUP
             BY jokecategory.jokeid 
         HAVING [COLOR="Blue"]COUNT(*) = 2[/COLOR] ) AS c2
INNER
  JOIN joke
    ON joke.id = c2.jokeid

see how that works?

now watch me change it from = 2 to >= 1

SELECT joke.joketext
  FROM ( SELECT jokecategory.jokeid
           FROM category
         INNER
           JOIN jokecategory
             ON jokecategory.categoryid = category.id
          WHERE category.name IN ( 'Knock-Knock' , 'Lawyers' )
         GROUP
             BY jokecategory.jokeid 
         HAVING [COLOR="blue"]COUNT(*) >= 1[/COLOR] ) AS c1
INNER
  JOIN joke
    ON joke.id = c1.jokeid

know what this one does?

3

Incredibly insightfull. Thank you very much!