mysql count query


Hi

I'm tying myself in knots trying to figure out a single query that returns elements from one table (a category) and the number of elements related to it (elements with said category id) within another table.

I have not introduced foreign keys in this instance.


cats table

id | catName
--------------
1 | cat1
2 | cat2
3 | cat3
4 | cat4

items table

id | name | catId
--------------------
1 | item1| 1
2 | item2| 2
3 | item3| 2
4 | item4| 1


I'd like something returned like:

cats.name | instances of cats.id in items

Hopefully that is clear, I'd be very appreciative of any help .
Posted On: Thursday 25th of October 2012 10:47:17 PM Total Views:  237
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




which mysql server to download??

hey friends...i request you all to pls help me out...im damn confused... im planning to make a website by March,2011 in which i will be using HTML/JSP and Mysql as d/b. however, i have a few doubts abt. Mysql.... 1) on the mysql site, there are many packages available to download. which one should i d/l. im using windows 7 and netbeans IDE 2) im confused whether i should use only the essentials package or the complete package wats d difference between both as far as query processing time is considered. my web application needs lowest query response time. 3) any tutorial site covering all the queries of Mysql....\t 4) is there any other d/b that i should use instead of Mysql im not willing to invest any money since im a student and i have previusly worked on oracle and sql. thanx a lot in advance.
VIEWS ON THIS POST

218

Posted on:

Thursday 25th October 2012
View Replies!

mysql privilege: does "select" allow me to join

After I changed my privileges to just select, this query ceased to function: Code SQL: SELECT base_SalesTable.* ,buckets.width ,buckets.teeth FROM base_SalesTable LEFT OUTER JOIN buckets ON base_SalesTable.name = buckets.name WHERE base_SalesTable.category = :category AND base_SalesTable.mincap = :mincap ORDER BY weight Happy Holidays
VIEWS ON THIS POST

160

Posted on:

Thursday 25th October 2012
View Replies!

Strange mysql query output from 3 table join

TableA (Author Table) author_id author_name TableB (Post Table) post_id author_id TableC (Earning Table) post_id (post id is not unique) post_earning I wanted to generate a report consists of per author total earning. author_id author_name total_earning (sum of earnings of all the posts by author) The SQL Query used: Code: SELECT a.author_id, a.author_name, sum(post_earning) as total_earnings FROM TableA a Inner Join TableB b on b.author_id = a.author_id Inner Join TableC c on c.post_id = b.post_id Group By a.author_id, a.author_name The Result I got is this : Code: ID user_login total_earnings 2 Redstar 13.99 7 Redleaf 980.18 10 topnhotnews 80.43 11 zmmishad 39.27 13 rashel 1248.34 14 coolsaint 1.66 16 hotnazmul 9.83 17 rubel 0.14 21 mahfuz1986 1.09 48 ripon 12.96 60 KHK 27.81 The sum of the total earning is actually 2863.22. But if i add all the values of the result table I get 2415. Where is the problem What I am missing I am sure SQL here can help me out. Here are the 3 tables i am using http://bit.ly/cNKS6k (TableA.csv) http://bit.ly/cJxRRN (TableB.csv) http://bit.ly/bNv9hs (TableC.csv)
VIEWS ON THIS POST

216

Posted on:

Thursday 25th October 2012
View Replies!

php mysql form date query help

php mysql form date query help hello all- I am trying to do a mysql database lookup by passing a begin date and end date from an html form. I have a date column in my database which is written to using the php function date("Y-m-d"); The date column is currently formatted as a mysql DATE field. The goal is to allow the user to lookup customer rows based between a begin and end date. Here is my string, which fails: $query='select * from '.$table.' where Date between `'.$Begin_Date.'` and `'.$End_Date.'` '; echo mysql_error(); the error message returns: Unknown column '2010-09-03' in 'where clause'
VIEWS ON THIS POST

278

Posted on:

Thursday 25th October 2012
View Replies!

How to connect to mysql remotely

I have a host running cpanel, and I have phpmyadmin. How do I connect to mysql remotely I would have a few websites tapped into one database... probably in a read only mode. Please point me in the right direction.
VIEWS ON THIS POST

343

Posted on:

Thursday 25th October 2012
View Replies!

mysql joining 2 tables (default values)

.. I came to situation where I have to join 2 tables on some other table: some_table: id domain table domains: id domaintype_id domain table domaintypes: id type This is how I join: domains ON some_table.domain = domains.domain domaintypes ON domaintype.id = domains.domaintype_id Now it happens that sometimes there is no domain for some_table row (so the domaintype will be NULL) and I want to be able to set this column default value 0 (when that happens).. How is it possible to do that Many
VIEWS ON THIS POST

255

Posted on:

Thursday 25th October 2012
View Replies!

mysql help, query hangs

SELECT a.id,a.dateid,a.ticker, case when a.close-(select b.close from hist b where a.ticker=b.ticker and b.dateid=a.dateid-1 ) > 0 then a.close-(select c.close from hist c where a.ticker=c.ticker and c.dateid=a.dateid-1 ) else '0.0' end from hist a where a.dateid > 3568;
VIEWS ON THIS POST

194

Posted on:

Thursday 25th October 2012
View Replies!

need help with simplifyling mysql queries.

ok im really not sure what this is called so im making a new thread. in my current website i need to select photos using 3 different mysql calls, is there a way to simplify them into one mysql call there are 3 types of photos i need selected and they are related to the current photo and "report" thing, so far i have been doing it this way: Code MySQL: "SELECT id FROM photos WHERE seq > :seq and reportid=:reportid and seq NOT LIKE '0' ORDER BY seq ASC LIMIT 1"; "SELECT id FROM photos WHERE id < :fotoid and reportid=:reportid ORDER BY id ASC LIMIT 1"; "SELECT id FROM photos WHERE seq=( SELECT MAX(seq) FROM photos WHERE reportid=:reportid) and reportid=:reportid ORDER BY ID ASC LIMIT 1"; but now i started wondering if its a bit excessive and could be possibly simplified into 1 mysql query
VIEWS ON THIS POST

275

Posted on:

Thursday 25th October 2012
View Replies!

Store array in mysql

Hi I have a table with a whole list of sites, and then each user can add those sites to his account, so basically i want to create a field in my users table that stores all the different sites that a person has added to their account. It's similar to facebook and adding friends... The best way i can think of to do this is storing the different id's of the sites in an array in one single field - don't know if this is possible or if there's a better way to do this Also, then how would I retrieve this list of sites in array form and then how would i add another to this list
VIEWS ON THIS POST

279

Posted on:

Thursday 25th October 2012
View Replies!

accidentally deleted mysql database and my database?

I think I may have deleted the mysql database as well as a practice database ("ijdb") that I just created. Here is what I'm being given as a result of my SHOW DATABASES command: Code: mysql> USE ijdb; ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'ijdb' mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) Right before this, I was trying to add data to a table within the "ijdb" database, and the data included several single and double quotes. I may have done something inadvertent involving quotes and escaping, because I couldn't get back to the [mysql>] command prompt. I closed down my Terminal window, tried to USE ijdb, then when I was given the error I entered the SHOW DATABASES command. If I bombed the mysql database is there anything I can do to recover it, or do I need to reinstall
VIEWS ON THIS POST

269

Posted on:

Thursday 25th October 2012
View Replies!

Replacing mysql value

So I am trying to update a table from a MYSQL database with values from another table. I want to replace the values for each row of Columns A and B in table 1 with the values for each row of Columns C and D in table 2. How do I do this
VIEWS ON THIS POST

296

Posted on:

Thursday 25th October 2012
View Replies!

mysql table design suggestion

Hi Please give me best suggestion. I am using mysql database. In one table daily i am going to insert 4-6 lac records.Half of the records are type 1 and other are type 2.There is one column which distingwishes record whether it is of type 1 or type 2. Before inserting these records i am truncating the table. Database have other table also. We are generating two report of type 1 and type2 using join query on other tables. Please let me know whether i should keep the same table for these 4-6 lac records of type 1 and type 2 or keep separate table of type 1 and type 2 . Using 2 separate , number of records are going to divide in two table 2-3 lac per table. Please give me the best suggestion.
VIEWS ON THIS POST

300

Posted on:

Thursday 25th October 2012
View Replies!

Moving data from one mysql database to another

, I have an access database that I converted to mysql this week. My new task is to combine some of these tables from the old database to the new database. The old system had 87 tables and the new database has 46. I have to move roughly 5 million rows of data but before that happens, I need to map the attributes first to the new database tables. The structues between the two are totally different. Can someone please give me some pointers Is there some sore of a mapping tool that I can use for this I'm up to my eyeballs in SQL right now.
VIEWS ON THIS POST

247

Posted on:

Thursday 25th October 2012
View Replies!

mysql LEFT JOIN very slow

my query takes almost 2 minutes to query which is very slow. My 'clicks' and 'links' tables both are constantly updated with new data so this could be a reason but I've also been reading about doing indexes but not familiar with this at all. What can I do to get the fastest query out of my sql. Code: SELECT l.title,l.url,l.stime,l.linkid FROM links l LEFT JOIN clicks c ON (c.linkid2 = l.linkid) ORDER BY l.linkid DESC
VIEWS ON THIS POST

273

Posted on:

Thursday 25th October 2012
View Replies!

Wanting to learn php and mysql, is there one I should learn before the other?

As you are likely to come across sql queries being used in php, it would be best to have a read of an sql book first. But if you were not going to use a database, it wouldn't matter if you didn't know some sql. Most php tutorials however use very simple sql in their examples. Rudy is an SQL god, and the free intro chapters you can get at from his book look okay for beginners to start with. Haven't seen the later chapters though. But that might change, of course.
VIEWS ON THIS POST

299

Posted on:

Thursday 25th October 2012
View Replies!

mysql server is not running

hello I am trying to run mysql server in linux. I am actually following this sitepoint book. "build own database driven website using php and mysql" by kevin Yank. I followed all the commands and mysql server is installed and it shows it is operating. after doing necessary things to launch mysql server first time I use the following command: shell#bin/mysqld_safe --user=mysql & it shows mysql daemon started again for testing mysql server I use the following command: shell#bin/mysqladmin -u root status then it shows some statistics of mysql server. Now I tried to post installation tasks means tried to put root passwd for mysql and I put command in bin directory of mysql installation mysql -u root mysql this command should be connects me to the newly installed mysql server as root user and chooses the database but it shows the following like bash:mysql:command not found \t\t\t\t\t\t what is the problem in here and anything wrong in bash file system can anyone please give me any idea about that which will be highly appreciated.
VIEWS ON THIS POST

345

Posted on:

Thursday 25th October 2012
View Replies!

adding mysql user - only root is able to login from a remote client.

hey all. need some quick help with user admin in mysql. so currently, I have a linux box with port 3306 open or mysql administration using navicat. I'm able to connect to the mysql server through navicat if I'm using the "root" username. For some reason, whenever I attempt to add a new user to mysql, i'm not able to connect to mysql through navicat or the mysql prompt. First, trying to add user for localhost: INSERT INTO user (Host,User,Password) VALUES('localhost','aaron',PASSWORD('abc123$')); Then grant privileges: GRANT ALL ON *.* TO aaron@localhost; Now when I try: $mysql -u aaron -p I enter in the password (abc123$) and it tells me access denied.. What am I missing I think If I get past this then the navicat client will work fine.
VIEWS ON THIS POST

306

Posted on:

Thursday 25th October 2012
View Replies!

set mysql variable using command

How to set mysql variable permanently using command without making changes in my.ini i cant find the my.ini file in c:/windows or in c:/mysql/mysqlserver/bin/
VIEWS ON THIS POST

275

Posted on:

Thursday 25th October 2012
View Replies!

mysql logic

here is my situation: i have a prepay online store and i advertised a contest to win a free code when buying at least 5 codes during 24.11-24.12 2008 period. now this is my table: Code: orders_id customers_id customers_name customers_company customers_street_address customers_suburb customers_city customers_postcode customers_state customers_country customers_telephone customers_email_address customers_address_format_id delivery_name delivery_company delivery_street_address delivery_suburb delivery_city delivery_postcode delivery_state delivery_country delivery_address_format_id billing_name billing_company billing_street_address billing_suburb billing_city billing_postcode billing_state billing_country billing_address_format_id payment_method cc_type cc_owner cc_number cc_expires last_modified date_purchased orders_status orders_date_finished currency currency_value i don't have a sql logic to : select all orders depending on date_purchased field which is in '2008-11-21 21:47:45' format, then calculate total number of orders for each of the customers in that period, depending on customers_id and orders_id i guess, then make a list of the customers that have the number of orders above or equal to 5 and finally randomly select one. any ideas
VIEWS ON THIS POST

260

Posted on:

Thursday 25th October 2012
View Replies!

mysql + pid / process count + increases

I have mysql 4.1.22 compiled on RHEL 4 and running fine. Over the time the threads / pids of mysql keep on increasing. For eg : ps -ef |grep mysql |wc -l 63 ps -ef |grep mysql |wc -l 101 The system is Intel Dual Core, RHEL4, 300 GB SATA HDD with 16GB RAM. Also I have observed that I cannot login into mysql shell when the process count goes higher beyond 100 meaning that it takes lots of time to enter into the mysql> shell I am using the my-innodb-heavy-4G.cnf as my.cnf with couple of modifications like max_connections=1000 skip-name-resolve innodb_file_per_table innodb_flush_method=O_DIRECT lower_case_table_names=1 expire_logs_days = 10 net_read_timeout=60 net_write_timeout=120 max_connect_errors = 100 innodb_locks_unsafe_for_binlog=1 innodb_flush_log_at_trx_commit=2 innodb_lock_wait_timeout=300 myisam_repair_threads = 10 innodb_file_io_threads = 20 innodb_thread_concurrency = 16 innodb_table_locks = 0 wait_timeout = 480 Rest of the settings remain same. The configure command used for compiling mysql is CFLAGS="-static -O3 -march=nocona -funroll-loops" \ CXX=gcc CXXFLAGS="-static -O3 -march=nocona -pipe -mmmx -msse -msse2 -mfpmath=sse,387 -funroll-loops -fno-exceptions -fno-rtti -fomit-frame-pointer -felide-constructors -mtune=nocona" \ ./configure \ --prefix=/usr/local/mysql \ --with-mysqld-user=mysql \ --libexecdir=/usr/sbin \ --sysconfdir=/etc \ --mandir=/usr/share/man \ --with-client-ldflags=-all-static \ --with-mysqld-ldflags=-all-static \ --with-thread-safe-client \ --with-unix-socket-path=/tmp/mysql.sock \ --localstatedir=/usr/local/mysql \ --enable-assembler \ --disable-shared \ --without-readline \ --without-debug \ --without-docs \ --without-bench and then make, make install and strip /usr/sbin/mysqld What am I doing wrong and why the process count is increasing over the time
VIEWS ON THIS POST

279

Posted on:

Thursday 25th October 2012
View Replies!