List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:March 23 2011 11:29pm
Subject:Select with counts of matching rows from another table...
View as plain text  
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.



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  
Steffan@stripped                             Phoenix, Az                                  USA
AIM    : SteffanC                     Skype : steffancline
GOOGLE : Steffan.Cline@stripped        MSN : steffan@stripped
YAHOO  : Steffan_Cline          ICQ : 57234309

Select with counts of matching rows from another table...Steffan A. Cline24 Mar
  • Re: Select with counts of matching rows from another table...hsv31 Mar
    • Re: Select with counts of matching rows from another table...Reindl Harald31 Mar