SEARCH YOUR SOLUTION HERE  

Left join that includes some values and excludes other values

I have an article database that associates articles with tags through the table "articles_tags" (this table has two columns: article_id and tag_id). Articles can have multiple tags. I would like to be able to select articles that have certain tags but also do not have others.

I have the part that only includes articles with certain tags working. In this example the query selects all articles that have been tagged with both tag id 10 and tag id 11:
Code: SELECT `articles`.`id` FROM `articles` LEFT JOIN `articles_tags` ON `articles_tags`.`article_id` = `articles`.`id` WHERE `articles_tags`.`tag_id` IN(10,11) GROUP BY `articles`.`id` HAVING COUNT(tag_id) = 2

How can I modify this to still include articles with both tag ids 10 and 11 but that also do not have tags ids 12 or 13 So for example:
article 1 (has tags 9, 10) - excluded
article 2 (has tags 9,10,11) - included
article 3 (has tags 9,10,11,12) - excluded
article 4 (has tags 9,10,11,13) - excluded

Posted On: Wednesday 24th of October 2012 01:25:43 AM Total Views:  198
View Complete with Replies




Related Messages:

3 tables with a left join   (111 Views)
Hi I have this select: Code: SELECT id_propiedad, arrival, departure, propiedad, link_ingles, link_esp, link_sve, duermen FROM buscador LEFT JOIN bookings ON propiedad = id_propiedad AND (('$arrival' BETWEEN arrival AND date_sub(departure, interval +1 day)) or ('$departure' BETWEEN date_sub(arrival, interval -1 day) AND departure) or (arrival = '$departure') or (arrival >= '$arrival' AND departure
Multiple left joins?   (109 Views)
I have three tables and am trying to get all the columns for every row returned from the first, as well as a COUNT(column) for the other two, regardless of if the counts returns anything or not. However, I am having unpredictable results. Here's my SQL: ---------- select posts.*, COUNT(referrer_id), COUNT(comment_id) FROM posts LEFT JOIN referrers ON referrer_id = posts.id LEFT JOIN comments ON comment_id = posts.id GROUP BY posts.id LIMIT 0,30 ------------ Is seems the second count function is lumping everything together in one row instead of displaying the count on a row-to-row basis. What exactly am I doing wrong Is LEFT JOIN incorrect for this scenario
Update with left join   (144 Views)
! Can anyone tell me the correct way to do this. I have an emailaddresses table and am turning on/off the enabled setting within it, based on an array of email addresses that are passed in. So I want to turn ON all of the emails passed in, and turn OFF all other emails. The problem I've got with my existing sql is when ALL emails are being turned on then there are NONE to be turn off, so the update fails because there are no "eoff". I hope that makes sense!! Regan Code: update emailaddresses as eon left outer join emailaddresses as eoff on eon.id = eoff.id and eon.email != eoff.email and eoff.main = '0' set eon.enabled = '1' ,eoff.enabled = '0' where eon.id = '37' and eon.email IN ('one@fred.com','two@fred.com')
Join 3 tables- inner, outer, left?   (167 Views)
I have the following three tables (*= primary key): user_address *address_ID user_address address_ID *user_ID address_ID user_name *user_ID user_name I need to: Code: SELECT ALL from user_name AS n user_address AS a, address_ID as i WHERE n.user_ID = i.user_ID i.address_ID = a.address_ID a.user_address = 'entered value' I know I need to use joins, but not sure which ones and syntax Any assistance is greatly appreciated...
Double count two left joins   (127 Views)
The following query is supposed to return articles, with a count for how many comments they have aswell as a score, which it is doing, but it is doubling the count at the moment. I've tried adding a DISTINCT to the count and that breaks the score and only returns articles with a score, I want it to return all articles even if they dont have any comments or scores. Code: SELECT article.*,COUNT(articlecomment.ID) comments, AVG(score) FROM article LEFT JOIN articlecomment ON article.ID = articlecomment.article_ID LEFT JOIN score ON article.ID = score.articleID GROUP BY article.ID ORDER BY article.ID DESC LIMIT 0,3
using left join - can't retrieve data from second table   (143 Views)
I am trying to retrieve details for an audio file from two different tables. The UPLOADS tables contains most of the information about the file, including whether it is linked to a release (CD, DVD etc). From the RELEASES table I need to retrieve the appropriate cover art for the release. Some audio files are not linked to a release, therefore I will sometimes need to retrieve data from the UPLOADS table when there is no matching release coverart in the RELEASES table. I have tried using a left join to achieve this and am able to retrieve all the relevant info from the UPLOADS table, but the path for the cover art is not being retrieved. Is my syntax faulty, am I using the right method of joining tables to achieve what I need Can anyone help me (I've included a bit of my PHP code, in case someone happens to spot an error outside of my query. Hope that's okay.) PHP Code: $id=$_GET['id']; if($id) { $result=mysql_query("SELECT uploads.release_id,uploads.upload_type,uploads.upload_timestamp,uploads.upload_name,uploads.upload_format,uploads.upload_path,uploads.upload_comment,release.release_id,release.release_coverart FROM uploadsLEFTJOINrelease ON uploads.release_id=release.release_id WHERE uploads.upload_id='$id'") orprint("Cannotfindfile."); while($row=mysql_fetch_array($result)) { $id=$row["upload_id"]; $type=$row["upload_type"]; $name=$row["upload_name"]; $format=$row["upload_format"]; $path=$row["upload_path"]; $comment=$row["upload_comment"]; $art_path=$row["release_coverart"]; $date =date("d.m.y",$row["upload_timestamp"]);
Inner join, left join, intersect   (124 Views)
is it possible to combine in one statement Problem: I have two table, some records in one table exist in the other table. I need all of the matching records in one table, and all of the matching records in both tables that intersect. I need one statment that produces this. Select * from tableA where condition = "tableA.condition1" select * from tableA, tableB where tableA.condition1 = tableB.condition1 The resulting table cannot have duplicates
Help me solve this puzzle - subselect? left join?   (160 Views)
I'm working on a query that has gone beyond my level of expertise and I'm hoping to get some help. Here's my best attempt at describing the situation/problem: relevant tables within the current database = title, oct_values, employee relevant tables in other database = action_history current query: SELECT sum(qty), sum(value*qty), slocation, class, a.user, name, substring(status_time,1,7) as month FROM title, oct_values, db2.action_history a, employee WHERE fkey = title.titlenum and a.user = employee.id and title.titlenum = a.titlenum and status_time like "2007-07-%" and field_id = 75 GROUP BY slocation, class, a.user, month; Problem: The only element within the action_history I'm able to use on my join is called "titlenum" but in this table, "titlenum" is not unique. However, I specifically need to return the data from one of those lines - and not the others. I need the value associated with the result that has the *smallest* id. Here's an example of that data: select * from db2.action_history where titlenum = 9685596; +---------------------+----------+--------+------+------------+----------+ | stamp | titlenum | status | user | old_status | id | +---------------------+----------+--------+------+------------+----------+ | 2007-07-01 09:40:22 | 9685596 | 70 | 41 | 90 | 19112934 | | 2007-07-01 09:40:45 | 9685596 | 85 | 41 | 70 | 19112935 | | 2007-07-01 21:15:24 | 9685596 | 90 | 0 | 85 | 19118541 | +---------------------+----------+--------+------+------------+----------+ I need to structure the query so that the results return with user 41 from the first row in these results. However, the query that I have now (seems to) always return the value from the last row. It seems that maybe a subquery might be necessary, but I haven't been able to come up with a query that gives me the results I'm looking for. Here's the first few lines of results as the query is now: | sum(qty) | sum(value*qty) | slocation | class | user | name | month | +----------+----------------+-----------+----------------+------+----------------+---------+ | 1 | 12.95 | 1 | new | 397 | First Last | 2007-07 | | 2 | 25.9 | 1 | new | 569 | First Last | 2007-07 | | 2 | 15.96 | 1 | sale | 41 | First Last | 2007-07 | | 1 | 7.98 | 1 | sale | 397 | First Last | 2007-07 |
Mysql query "left outer join AND count()"   (94 Views)
, I need to write a query which contains a LEFT OUTER JOIN which retrieves the all the results from "job" table and counts the number of a specific attribute from "applications" table,,, plus there is another join between two other tables (categories and category_type) in the same query. I am prety sure I can get the results within one query, The first query that I wrote counts the results correctly: $query = "SELECT job.job_id, job.job_title, employer.employer_id, employer.employer_company_name, job.job_location, job.job_close_date, COUNT(applications.job_id) AS counter FROM job LEFT OUTER JOIN applications ON job.job_id = applications.job_id JOIN employer ON job.employer_id = employer.employer_id GROUP BY job.job_id ORDER BY counter DESC"; But in the second part of the query, when I add the other tables,the COUNT(), retrieves wrong results: $query = "SELECT job.job_id, job.job_title,category_type_name, employer.employer_id, employer.employer_company_name, job.job_location,job.job_close_date, COUNT(applications.job_id) AS counter FROM job LEFT OUTER JOIN applications ON job.job_id = applications.job_id JOIN employer ON job.employer_id = employer.employer_id LEFT OUTER JOIN categories ON job.job_category = categories.category_type LEFT OUTER JOIN category_type ON categories.category_type = category_type.category_type_id GROUP BYjob.job_id ORDER BY job_category, counter DESC"; Any ideas
Optimizing left outer join query   (123 Views)
My database query is taking about 15 Seconds to show me the result from mysql two tables. I want to optimize the performance of the result. We want to show total number of items not present in table "products" but present in table "vendor_products" Code: # # Table structure for table 'vendor_products' # CREATE TABLE `vendor_products` ( `vpid` int(10) unsigned NOT NULL auto_increment, `vendor_id` int(10) unsigned default NULL, `mfg_no` varchar(25) default NULL, `price` decimal(10,2) default NULL, PRIMARY KEY (`vpid`), KEY `mfg_no` (`mfg_no`), KEY `vendor_id` (`vendor_id`) ) TYPE=MyISAM; # # Table structure for table 'products' # CREATE TABLE `products` ( `pid` int(10) unsigned NOT NULL auto_increment, `mfg_no` varchar(25) default NULL, `price` decimal(10,2) default NULL, PRIMARY KEY (`pid`), UNIQUE KEY `mfg_no` (`mfg_no`) ) TYPE=MyISAM; Code: SELECT count(*) as rejected_products FROM `vendor_products` as v left outer join products as p on (v.mfg_no = p.mfg_no) where p.mfg_no is null and vendor_id=1002; The Explain requery shows :- Code: table,type,possible_keys,key,key_len,ref,rows,Extra v,ALL,vendor_id,NULL,NULL,NULL,315033,Using where p,ref,mfg_no,mfg_no,26,v.mfg_no,10,Using where; Using index total records in tables :- vendor_products = 820044 products = 409475
Limiting results from a left join   (96 Views)
I've tried searching on google and here but I'm not even sure whether I've found what I'm looking for when I found something.... anyway basically I have two tables one with three rows one with several. I am left joining the second table with the first one based on shared data (a id number that matches in both tables). I run mysql_fetch_array until I have read all the data but heres the problem. Each row in table one is being returned more than once as it matches more than one row in table 2. For each row in table 1 I want to get only the first matching row in table 2. Does that make ANY sense Hope so. Any pointers in the right direction would be greatly appreciated
mysql: left vs inner joins   (213 Views)
I'm sure this has probably been asked before and, don't worry, I have searched the forums and found some answers. BUT does anyone know any links to exampls of the two joins I've got explanations, sure. But I feel I'd better understand what left joins do (over inner ones) if I could see an example of the output you'd get on a simple DB. Unless anyone has the time or inclination to write one
assign allias on 2 left joins   (117 Views)
Forums, I have this table user id first type 1 jane A 2 john B order_table id a_id b_id 1 1 2 I left joined order_table.a_id to user.id and order_table.b_id to user.id SELECT first FROM user LEFT JOIN user ON order_table.a_id = user.id AND order_table.b_id = user.id WHERE order_table.id = 1 I need to create an alias for "first" to differentiate between types. Any ideas
Mysql left join help   (117 Views)
, I have a problem in mysql that is quite difficult to explain, but I hope you will understand me... even more hard since I don't normally speak english So I have 3 tables: room, people and a intersection table called roomPeople Room & people contain an id, and roomPeople contain the id of the 2 others rooms plus the date they left I want to be able to select all rooms, and if the date is not expired the person in the room at this moment I hope you can understand what I just said Thx
mysql left join and keep original row   (133 Views)
I need to get the join as well as a record for the original table. In my case Example: Table 1 userID, name 1, Joe 2, Jim 3, Jane Table 2 userID, percent 1, 25 1, 50 2, 33 I need the following output userID, name, percent 1, Joe, 25 1, Joe, 50 1, Joe, NULL 2, Jim, 33 2, Jim, NULL 3, Jane, NULL I can get the percentages if they have them and the NULL if they dont, I just cant get the original record with NULL for users that have percentage.
Join/left join on MySQL request with v5.0.91   (113 Views)
Hi all, my hosting provider upgraded MySQL to 5.0.91, I used to have some scripts that were working under the old version of MySQL (don't remember the version I was using). This script was using left joins to get the work done, but it seems that the new version does not work the same way. What I have is a list of people with departement and a role people table - id - people_name - people_department_id - people_role_id department table - id - department_name role table - id - role_name My script was building a report, by default, all departments were listed, so I created a simple while loop printing out the department names, simple. Now, I want to list people inside that department (people.people_department_id = department.id) grouped by their roles, but I want the role to be printed once at the start of the while loop AND if the role is empty of people, I don't want to see the role to be printed; DEPARTMENT #1 - DG (1 people listed under) - Executive (5 people listed under) - Worker (18 people listed under) DEPARTMENT #2 - DG (1 people listed under) - Executive (0 people, SO NOT PRINTED AT ALL) - Worker (4 people listed under) DEPARTMENT #3 - DG (0, SO NOT PRINTED AT ALL) - Executive (4 people listed under) - Worker (0, SO NOT PRINTED AT AL) How can I achieve that I hope I'm clear enough, don't hesitate to ask questions.
Count left join   (113 Views)
I am having trouble getting a comment count from the comments table that I am left joining. My query is something like SELECT * FROM books LEFT JOIN comments ON books.id = comments.book_id How do I get the count of the comments for each particular book (books.id)
Test for id in joined table   (107 Views)
I have two tables, one for articles and one that keeps a list of images, if any, that are associated with an article: Code: -------------- articles -------------- id section_id title -------------- -------------- articles_images -------------- id article_id -------------- I would like to run a query that returns article ids within a certain section and only include articles with images. This is what I have for returning articles from section 1: Code: SELECT `articles`.`id` FROM `articles` LEFT JOIN `articles_images` ON `articles_images`.`article_id` = `articles`.`id` WHERE `articles`.`section_id`= 1 AND `articles_images`.`id` IS NOT NULL The problem is if an article has multiple images that article id gets returned several times. How do I simply test if an article has any images
Dividing in left joined query   (104 Views)
Can not get my head around this. I've been searching but unabled to put myself in the right direction. Perhaps any one of you can give me the final push. Links to ref's, or any word is welcome. Code: SELECT channels.icon, channels.title, channels.siteurl, item.cid, sum( item.clicks ) AS `clickcount` , COUNT( item.id ) AS `itemcount` , (item.clicks / COUNT( item.id ) *10000 ) AS grade FROM item LEFT JOIN channels ON item.cid = channels.id WHERE added > '2009-01-01' GROUP BY cid ORDER BY grade DESC LIMIT 0 , 10 Results of clickcount and itemcount are correct. But i'm not getting a result in the grade colom. Any idea Additional question: Why am I not allowed to use eg. 'clickcount' in the calculation part Thx in advance. , Thx 4ur reply, - Group by: indeed this is item.cid - Adding the sum to the calculation solves the problem. (thx for that) - 'added' commes from the item table. - The choice of LEFT JOIN I can't really explain. That would make it like this, I guess. But what does the "AS d" do Sorry for the questions, but my curiousity is not as easy satisfied as programming needs. ;-) Learn I must. Code: SELECT icon , title , siteurl , cid , clickcount , itemcount , itemcount / clickcount * 10000 AS grade FROM ( SELECT channels.icon , channels.title , channels.siteurl , item.cid , SUM( item.clicks ) AS `clickcount` , COUNT( item.id ) AS `itemcount` FROM item LEFT JOIN channels ON item.cid = channels.id WHERE item.added > '2009-01-01' GROUP BY cid ) AS d ORDER BY grade