SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with a query using lookup table example

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aj7000 View Post
    *** MY QUESTION: What is the query (or direction to go in) in order to list Jokes in the "Knock-Knock" AND "Lawyers" Catergories ?
    you have to count the relationship rows and make sure both of them were present
    Code:
    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 COUNT(*) = 2 ) AS c2
    INNER
      JOIN joke
        ON joke.id = c2.jokeid
    see how that works?

    now watch me change it from = 2 to >= 1
    Code:
    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 COUNT(*) >= 1 ) AS c1
    INNER
      JOIN joke
        ON joke.id = c1.jokeid
    know what this one does?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Incredibly insightfull. Thank you very much!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •