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:
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!
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
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
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, 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.
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
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
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
[COLOR="Blue"] WHERE t.tag LIKE 'foo%'
OR t.tag LIKE 'bar%'[/COLOR]
GROUP
BY n.id DESC
HAVING COUNT(*) = 2
best wait 'til December