SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Find Rows Without Match in 2nd Table, and Select Rows that Only Occur Once in 1st

    Here is the mysql query I'm using to see which rows in our "tags" table have no matching row in our "taginfo" table.

    SELECT *
    FROM `tags`
    WHERE `snub` NOT
    IN (

    SELECT `snub`
    FROM `taginfo`
    )

    Now, I want to take this query a step further. I'm hoping to select not only the rows in the "tags" table where the "snub" column has no match, but I want to select the rows that only occur once. For instance:

    list of "snub" variables in the "tags" table

    comedy
    romance
    actor name
    actor name
    romance
    foreign

    So, in this case, it would select rows with comedy and foreign as the snub, since they only appear once.

    I can't figure out how to achieve this. (my final goal is to delete all rows that have single instance not matching).

    All feedback appreciated!
    Cheers
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  2. #2
    SitePoint Member
    Join Date
    Dec 2013
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can do it like this,for example:
    SELECT * FROM `tags` GROUP BY `snub` HAVING COUNT(`snub`)=1;

  3. #3
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know that query and have been trying to apply that to the multi-table one above, but without success.
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  4. #4
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, that combo worked. I thought I already attempted this, but it worked this time around:

    SELECT *
    FROM `tags`
    WHERE `snub` NOT
    IN (

    SELECT `snub`
    FROM `taginfo`
    )
    GROUP BY `snub`
    HAVING COUNT( `snub` ) =1
    LIMIT 0 , 30
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.


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
  •