Re: Need help with query
2011/03/15 17:51 -0500, LAMP Let's say there is a table orders (simplified, of course) CREATE TABLE orders ( item_id int, org_id int, ) ENGINE=MyISAM Need to select all (distinct) org_id they have item_id 34, 36, 58 and 63. All of them, not only some of them. Result is org_id=2607 and org_id=1520 I can have it by select org_id from orders where item_id in (34, 36, 58, 63) group by org_id having count(org_id)=4 I now noticed the aggregate function GROUP_CONCAT: select org_id,GROUP_CONCAT(DISTINCT item_id, ORDER BY item_id) AS itemset from orders where item_id in (34, 36, 58, 63) group by org_id having itemset = '34,36,58,63' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
CONNX Webinar for MySQL
CONNX Webinar Learn about integrating all of your enterprise data into MySQL databases. CONNX is easy to install and easy to use, yet sophisticated and powerful enough to solve all of your enterprise wide integration needs through a single product. The CONNX platform integrates MySQL with more than 50 different databases and platforms; and, easy to use modules for Replication and Transformation (full ETL capability). This provides a complete, high performance, and functional solution when populating an Operational Data Store, Data Mart or Data Warehouse. Date: April 5, 2011 Register http://www.connx.com/emailers/msq042011_info.htm Now Time: 10:30am PT / 11:30am MT 12:30pm CT / 1:30pm ET Join us in a live presentation and interactive QA session and discover the flexibility of using CONNX with MySQL.
Select with counts of matching rows from another table...
I have 3 tables Table ³groups² groupid, groupname Table ³agmap² groupid, articleid Table ³articles² articleid, articletopic, articlebody The relation is that articles can have groups attached to it via the map table. I can insert this and work it out fine. The issue is when I want to pull the groups into a list of checkboxes and check them accordingly upon edit. So, this is what I have as a basis to work on assuming I am polling article #36. Select *, if(b.articleid=36,1,0) as checked from groups g Left join agmap a on g.groupid=a.groupid Left join articles b on a.articleid=b.articleid Order by g.groupname This will spit out the groups with all the articles mapped to the groups. What I need is to get back a list of groups with some indicator if there is a match to a particular article id. The results should look something like this: groupname articleid checked Group1 null 0 Group2 36 1 Group3 36 1 Group4 null 0 I tried adding ³group by groupname² which will give me back the 4 groups which is fine, but the checked column is wrong because it always grabs a lower numbered article id that is matched to the group although the ³checked² column will be right, in this case 0. This is a rough example of what it looks like without ³group by² groupname articleid checked Group1 26 0 Group1 14 0 Group2 1 0 Group2 3 0 Group2 36 1 Group3 36 1 Group4 null 0 I know there has to be a way to make it work right but its just not there Another way of explaining it is, I am trying to get a list of the groups, in order, and get a 1 or 0 in the checked column if a specific article is linked to the group (row) or not. Thanks Steffan --- T E L 6 0 2 . 7 9 3 . 0 0 1 4 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline stef...@execuchoice.net Phoenix, Az http://www.ExecuChoice.net USA AIM: SteffanC Skype : steffancline GOOGLE : steffan.cl...@gmail.comMSN : stef...@hldns.com YAHOO : Steffan_Cline ICQ : 57234309 --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
