From: Steffan A. Cline Date: March 23 2011 11:29pm Subject: Select with counts of matching rows from another table... List-Archive: http://lists.mysql.com/mysql/224701 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable I have 3 tables =AD =20 Table =B3groups=B2 groupid, groupname =20 Table =B3agmap=B2 groupid, articleid =20 Table =B3articles=B2 articleid, articletopic, articlebody =20 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. =20 Select *, if(b.articleid=3D36,1,0) as checked from groups g Left join agmap a on g.groupid=3Da.groupid Left join articles b on a.articleid=3Db.articleid Order by g.groupname =20 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: =20 groupname articleid checked Group1 null 0 Group2 36 1 Group3 36 1 Group4 null 0 =20 I tried adding =B3group by groupname=B2 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 =B3checked=B2 column will be right, in this case 0. =20 This is a rough example of what it looks like without =B3group by=B2 =20 groupname articleid checked Group1 26 0 Group1 14 0 Group2 1 0 Group2 3 0 Group2 36 1 Group3 36 1 Group4 null 0 =20 I know there has to be a way to make it work right but its just not there=8A 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 =20 Steffan@stripped Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC Skype : steffancline GOOGLE : Steffan.Cline@stripped MSN : steffan@stripped YAHOO : Steffan_Cline ICQ : 57234309 ---------------------------------------------------------------