Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

SQL Query

Codes here !

Moderators: macek, egami, gesf

SQL Query

Postby ruturajv » Wed Jun 25, 2003 8:27 am

Hi,
I have a tables as tblGroups, tblMessages

tblGroups is as follows
+----------+----------+----------+
| groupid |groupname| grpdesc |
+----------+----------+----------+
| 1 | abc | descrip |
+----------+----------+----------+
| 2 | abc | descrip |
+----------+----------+----------+
| 3 | abc | descrip |
+----------+----------+----------+
| 4 | abc | descrip |
+----------+----------+----------+
| 5 | abc | descrip |
+----------+----------+----------+
and so on

tblMessages is as follows
+----------+----------+----------+
| groupid | text | msgdesc |
+----------+----------+----------+
| 1 | abc | descrip |
+----------+----------+----------+
| 3 | sdf | descrip |
+----------+----------+----------+
| 1 | fgg | descrip |
+----------+----------+----------+
| 1 | cbc | descrip |
+----------+----------+----------+
| 1 | cbc | descrip |
+----------+----------+----------+
and so on

Now i want to find the the no of messages in each group

so what query should i use to obtain something like this
Also if want to show groups which don't have any messages into it eg the groupid 2, 3 ,and others

+----------+----------+----------+---------------+
| groupid |groupname | grpdesc | totalmessages |
+----------+----------+----------+---------------+
| 1 | abc | abc | 4 |
+----------+----------+----------+---------------+
| 3 | abc | abc | 1 |
+----------+----------+----------+---------------+


If anyone can help, thanks
User avatar
ruturajv
php-forum Super User
php-forum Super User
 
Posts: 1280
Joined: Sat Mar 22, 2003 9:42 am
Location: Mumbai, India

Postby Flood » Wed Jun 25, 2003 8:36 am

hi!

I guess you should have something like:

SELECT g.groupid, groupname, grpdesc, COUNT(m.groupid)
FROM tblGroups g, tblMessages m
WHERE g.groupid = m.groupid
GROUP BY g.groupid;

I have written it pretty quickly, I might have mistaken... but the general idea is to use GROUP BY...

If there are no message, then no line will be displayed. If you want one, use LEFT JOIN instead of the WHERE...
-->
SELECT g.groupid, groupname, grpdesc, COUNT(m.groupid)
FROM tblGroups g
LEFT JOIN tblMessage AS m ON (m.groupid=g.groupid)
GROUP BY g.groupid;

Incidentally, don't you mind having redundancy within your tblMessages table? It sounds strange to me...

/Flood
Flood
New php-forum User
New php-forum User
 
Posts: 23
Joined: Tue Jun 24, 2003 4:49 am


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.