SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Narrowing search on tags

    Hi guys,

    I'm having problems getting this query to work. Basically, I have a table of news articles (the only relevant field is news.news_id), I then have a table of tags (tags.tag_id and tags.tag are the two fields to look at) and then a third table called newshastag which has only two fields; newshastag.news_id and newshastag.tag_id. This table links all of the articles to the tags. I got a query something like this:

    Code MySQL:
    SELECT
           `n`.*
      FROM `news` `n`
     LEFT
      JOIN `newshastag` `nht`
        ON `nht`.`news_id` = `n`.`id`
     LEFT
      JOIN `tags` `t`
        ON `t`.`tag_id` = `nht`.`tag_id`
     WHERE `t`.`tag` = 'man:apple'
       AND `t`.`tag` = 'iphone'
    ORDER
        BY `n`.`id` DESC

    Now, I can see why that won't work, but I can't get it to work the way I want it to. I need to see all news articles that have the 'man:apple' tag AND the 'iphone' tag, not one or the other (so IN() is out, so to speak).

    I'm sure it's a dead simple fix, but I'm stuck! Please help!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    I need to see all news articles that have the 'man:apple' tag AND the 'iphone' tag
    then you should, for starters, be using INNER JOINs

    LEFT OUTER JOINs imply "gimme the news with or without these tags"

    oh, and please, ditch the backticks, thanks
    Code:
    SELECT n.*
      FROM news AS n 
    INNER
      JOIN newshastag AS nht    
        ON nht.news_id = n.id 
    INNER
      JOIN tags AS t
        ON t.tag_id = nht.tag_id 
     WHERE t.tag IN ( 'man:apple' , 'iphone' )
    GROUP
        BY n.id DESC
    HAVING COUNT(*) = 2
    note the use of IN() which is equivalent to OR

    it has to be OR because the WHERE clause works only on one row at a time, and a single tag value cannot be both things at the same time

    the "and" condition is enforced by the HAVING clause, which ensures that both tags are present for each GROUP BY column

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Brilliant, thanks. TBH, I realised that I needed HAVING in there shortly after I posted, but I've been getting some weird other results still that I need to look further in to. I'll test it as you wrote it however and see how it goes, thanks.

    BTW, what's the problem with the back-ticks?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    BTW, what's the problem with the back-ticks?
    all noise, no signal

    in fact, they tend to obscure the signal
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Not entirely sure I understand, but I'm getting the impression you're saying they're pointless and make it harder to read?

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    dats about it.

  7. #7
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK, cheers.

    BTW, with inner joins it doesn't work properly. Likwise, using IN() doesn't work. The SQL is dynamically generated for an AJAX call and when the tag is being typed the EQUALS becomes a LIKE with % on the end until a semi-colon is specified. All I had to do to the original SQL was padd the HAVING clause. Change it any more and it just doesn't work. I don't uinderstand why, but that's the truth.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    BTW, with inner joins it doesn't work properly.
    oh yes it does

    please show the INNER JOIN query that didn't work, as well as whatever other query you have that did work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    oh yes it does
    too soon for panto rudy best wait 'til December (maybe it's a UK thing? if so, sorry).

    http://en.wikipedia.org/wiki/Pantomime

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Off Topic:

    funny you should mention panto, here in canada we are approx 10 mths behind the british schedule of coronation street, and just last week or so, claire peacock and john stape did their panto in the rovers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'll double check when I head off to work in a bit, but I'm sure it didn't work for what I'm doing. Basically, this SQL is dynamically generated by a PHP script that handles an AJAX call and returns a JSON array of news articles that are displayed on the next page. As you type it searches, so as you're typing it will on WHERE t.tag LIKE 'string%', until the tag is complete, at which point it changes to WHERE t.tag = 'string'. The HAVING clause is not present in the SQL until there are more than one tags specified, else it doesn't work correctly. If there are no tags specified, the last 50 news articles are displayed. Because of the use of LIKE, I can't just do an IN() either, unless there's a way to use wildcards in IN(), which I wasn't aware there was. Basically, I have no doubt that your re-written SQL would work, but it doesn't appear to in my case. I'll have a bit more of a play with it in a bit mind you and I'll see what I can do with it.

    Thanks again for your help though; it's much appreciated

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    Because of the use of LIKE, I can't just do an IN() either...
    Code:
    SELECT n.*
      FROM news AS n 
    INNER
      JOIN newshastag AS nht    
        ON nht.news_id = n.id 
    INNER
      JOIN tags AS t
        ON t.tag_id = nht.tag_id 
     WHERE t.tag LIKE 'foo%'
        OR t.tag LIKE 'bar%'
    GROUP
        BY n.id DESC
    HAVING COUNT(*) = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that's what I'm doing now, thanks


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
  •