----- Original Message ----- From: "iNFERNo" <[EMAIL PROTECTED]> To: "mysql" <mysql@lists.mysql.com> Sent: Wednesday, April 06, 2005 5:23 PM Subject: I need some help
> Hi, > > I have a problem: I am using mysql 4.0.24 and I need to make > some reports from a database: > > mysql> describe events; > > +-------------+------------------+------+-----+---------+----------------+ > > | Field | Type | Null | Key | Default | Extra | > > +-------------+------------------+------+-----+---------+----------------+ > > | id | int(10) unsigned | | PRI | NULL | auto_increment | > > | user_id | varchar(55) | | | 0 | | > > | dep_id | int(15) | | | 0 | | > > | event_id | int(15) | | | 0 | | > > | year | int(15) | | | 0 | | > > | day | int(15) | | | 0 | | > > | month | int(15) | | | 0 | | > > | ev_status | int(11) | YES | | 0 | | > > | ev_type | int(11) | YES | | 0 | | > > | ev_priority | int(11) | YES | | 0 | | > > +-------------+------------------+------+-----+---------+----------------+ > > I need something to get: > > | USER_ID | DEP_ID | EV_STATUS = 0 | EV_STATUS = 1 | EV_STATUS = 2 | > EV_STATUS = 3 | EV_STATUS = 4 | EV_STATUS = 5 | EV_STATUS = 6 | > > user_1 1 COUNT COUNT > COUNT COUNT COUNT > COUNT COUNT > > user_2 1 COUNT COUNT > COUNT COUNT COUNT > COUNT COUNT > > user_3 2 COUNT COUNT > COUNT COUNT > COUNT COUNT COUNT > > . > . > . > > > The problem is that the way I am getting this now is with a query > for each user and for each ev_status and the output is in PHP. Is there > a better way to do this ? I am only a beginner in MySQL and want to > learn more and improve. > First of all, congratulations on identifying your version of MySQL and giving the definition of your table. That is an excellent start to getting an answer to your question. Far too many people post here without identifying their MySQL version or giving the definition of their tables, making it very difficult to answer their questions without having to ask many followup questions. Unfortunately, there is at least one problem evident in your table definition. It appears that you don't have any primary key on the table! As a newcomer to MySQL, you may not even know what a primary key is. Do you have any training with data design, particularly normalization? If not, you should definitely find out what a primary key is. In my opinion, which is based on 20+ years with relational databases, choosing a good primary key is vital to having a good database design. If you don't choose good primary keys - or worse, if you don't have any primary keys at all - you are going to have many serious problems with your database. Giving you the answer to your question depends very strongly on what the primary key of your table is. In other words, you really need to choose and define the primary key before anyone can answer your question correctly. Are you able to perform a normalization so that you can choose a primary key for this table? If not, perhaps you can use your favourite search engine to find a tutorial on "database design" or "normalization" to learn the technique. Then, once you have chosen a primary key, post again with the full definition of the table, like you did in this post, but this time including a primary key definition. It would also help if you provided a small amount of the data in your table so that we can get a good understanding of the meaning of the data. With that information, I think we can do a good job of helping you work out the SQL that will do what you want to do. I should warn you that if you have no training in normalization of a data model, it may seem difficult and time consuming. In fact, it may take you a couple of days or more to find a good tutorial and for you to work your way through it. That may seem like a lot of effort but, believe me, the time you invest in studying normalization now will pay for itself many many times over in the future as you build databases. You'll also find that you can normalize data models very quickly and often in your head once you get a bit of practice with it. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.3 - Release Date: 05/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
