Select with counts of matching rows from another table...

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@ExecuChoice.net Phoenix, Az
http://www.ExecuChoice.net USA
AIM : SteffanC Skype : steffancline
GOOGLE : Steffan.Cline@gmail.com MSN : steffan@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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Select with counts of matching rows from another table...

>>>> 2011/03/23 16:29 -0700, Steffan A. Cline >>>>
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
<<<<<<<<
Why not restrict it to '36': 'WHERE b.articleid = 36'?

As for the grouping, as it says in its helptext about MySQL s permissive 'GROUP BY', the choice for any thus allowed field --one forbidden by the standard-- is random. (It happens I have a case where that is just what I want: I do not care which value is shown, as long as it goes with the rest.) Maybe you want 'articleid', too, in the grouping?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Select with counts of matching rows from another table...

--------------enig6C555A74DAD81EC989EB93A3
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable


Am 31.03.2011 17:53, schrieb S?ndor Hal?sz:
>>>>> 2011/03/23 16:29 -0700, Steffan A. Cline >>>>
> 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
> <<<<<<<<
> Why not restrict it to '36': 'WHERE b.articleid =3D 36'?
>=20
> As for the grouping, as it says in its helptext about MySQL s permissiv=
e 'GROUP BY',=20
> the choice for any thus allowed field --one forbidden by the standard--=
is random.=20
> (It happens I have a case where that is just what I want: I do not care=
which value=20
> is shown, as long as it goes with the rest.) Maybe you want 'articleid'=
, too, in the grouping?

instead of writing me since weeks private mails that you do not want
gpg-signed mails and how hard it is for you to not ignore .asc-attachment=
s
you should learn to use your mail-client and how to quote answers

original-text followed by <<<<<<<< is NOT quoting!


--------------enig6C555A74DAD81EC989EB93A3
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk2UwFoACgkQhmBjz394Ann7ggCdFzDnfYUHafSbKOTs9/yV vRYG
cr0AoINWc2ByQjPX3IZ7O+sZ87Sk35OI
=IFkO
-----END PGP SIGNATURE-----

--------------enig6C555A74DAD81EC989EB93A3--