Can't figure out how to create complex query with group by and count


I have a table usertags:
usertags
------------
- usertagid
- usertagsiteid
- usertagtext

I want to select the usertagtext and count of usertagtext grouped by usertagtext. I only want it to show usertagtext that also have a match in the usertagsiteid to a given value for usertagtext.

Not sure if that makes sense so here is an example:
usertagid | usertagsiteid | usertagtext
1 | 1 | 'search'
2 | 1 | 'search engine'
3 | 2 | 'news'

If i feed the query the usertagtext 'search' it should list:
'search engine', 1

If i feed the query the usertagtext 'search engine' it should list:
'search', 1

If i feed the query the usertagtext 'news' it should not return any results.

Here is what I have so far but it doesn't accept the usertagtext input into it:
Code: SELECT DISTINCT usertagtext, COUNT(usertagtext) AS usertagsum FROM usertags GROUP BY usertagtext ORDER BY usertagsum DESC LIMIT 20 Let me know if that still doesn't make sense, I'm having a hard time trying to describe it. Hopefully you can figure out what I am looking for based on my examples.
Posted On: Thursday 25th of October 2012 10:43:01 PM Total Views:  164
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Can't figure out which join syntax to use

A co-worker of mine, while trying to help me solve a problem, noticed that my sql statement was set up as Code: select t1.* from t1,t2 where t1.id=t2.id Obviously the above is a simplified example, but he said that this was a cartesian join and that the query first matches all of the rows from t1 to all of the rows from t2 and then strips them down based on the where clause. I told him that I never knew that to be the case. I'd have no problem re-writing the query as Code: select t1.* from t1 inner join t2 on t1.id=t2.id but couldn't get any confirmation that the latter is more efficient than the former from google searching. I was always lead to believe that the latter was ANSI sql and the first one is not always accepted by all sql engines. Anyway, anybody have any thoughts on this
VIEWS ON THIS POST

111

Posted on:

Thursday 11th October 2012
View Replies!

Multiple results when I only want one - can NOT figure this out

I'm having trouble at work with this and I just can not figure it out. I hope I can explain it correctly. Two tables table1 and table2 I need to trim up column C from table one with LEFT( (TRIM(LEADING '00' FROM C) ) , 7) to get 7 digits without the leading 00 I need to use this to match it to table2.D Here is where I'm stuck If there is a match it obviously returns a match BUT if there isn't a match I need ot try something else OR concat( LEFT( (TRIM(LEADING '00' FROM C) ) , 6),0) = table2.D cut off the last of the 7 digits and replace it with Zero Then return a result. My problem..... I get two results back not one if the 7 digits work. I get one result back if the seven digits didn't work. I have tried Select distinct and that didn't work either. I have no experience with stored procedures so that is out. I just can't figure this out. Smart people can you help to get my boss off my back My code: use DB; select table1.A,table1.B,table1.C, LEFT( (TRIM(LEADING '00' FROM C) ) , 7) AS Cresults, table2.A, table2.B, table2.C, table2.D from table1,table2 where LEFT( (TRIM(LEADING '00' FROM C) ) , 7) = table2.D OR concat( LEFT( (TRIM(LEADING '00' FROM C) ) , 6),0) = table2.D
VIEWS ON THIS POST

113

Posted on:

Friday 12th October 2012
View Replies!

Remote mysql server not recognizing cPanel configured database users

Setup: Cpanel/WHM Apache server, with Centos Remote mySQL server running 64 bit LInux and 64 bit mySQL Configured as a remote mySQL server, though WHM of Apache server. After moving a few sites, I find that the mySQL server is not recognizing database users configured in cPanel. Could still back out, but would rather move forward. Recomendations Thank you!
VIEWS ON THIS POST

177

Posted on:

Friday 12th October 2012
View Replies!

Mysql Reconfigure problem

I have mysql 4 and mysql 3 on my couple of servers.. Both of these versions have been installed from rpms. Now I have incluede Arabic Charsets into the database . Now after adding the charsets, I need to reconfigure the mysql.. I can not configure manually by following commands: ./configure --with-extra-charset=win1256 make make install becoz its RPM and not source installation.. So do I reconfigure the RPMs now to add the arabic charset Please help
VIEWS ON THIS POST

100

Posted on:

Friday 12th October 2012
View Replies!

AES_DECRYPT cant seem to show stored values

Ok I have sucessfuly created a page that insterest data using the AES_ENCRYPT. Now I am working on another page that will show the data. I dont through any erros but I dont see the information either. I am using Mysql 5.0 and I use dreamweaver cs4. Here is the code a am using. [code] $query_Recordset1 = "SELECT AES_DECRYPT(password, 'text_key') AS decrypt FROM content"; $Recordset1 = mysql_query($query_Recordset1, $EevokePages) or die(mysql_error()); $row_Recordset1 = mysql_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysql_num_rows($Recordset1); > Untitled Document [code] Can anyone help me out here I am stuck
VIEWS ON THIS POST

235

Posted on:

Friday 12th October 2012
View Replies!

Problems with creating stored procedures, can't figure out the syntax

so im trying to make some stored procedures but im still stuck at the first procedure still. SQL Code: Original - SQL Code use fromScratch; DROP PROCEDURE IF EXISTS personsGet; CREATE PROCEDURE personsGet () SELECT [id], [name], [lastname] FROM persons;
VIEWS ON THIS POST

158

Posted on:

Friday 12th October 2012
View Replies!

Can't figure out a great way to do this

Ok, so i have a profile page for my members, that will list the latest activity. Though, I always was told that doing mysql_query inside of a while loop (that is a mysql_query, or just in general) is a bad idea - and to use joins. So, I already have a database field, one for each type of upload (texts, pics, quotes, videos) and then I just made an activity table (lists the member's ID, post ID - to get that info - type (texts, pics, quotes, etc.) and activity (because they can upload, like, comment, dislike). Though, how can I go about getting JUST the uploads, yet getting information from all 4 other tables that could be the possible "types". Hopefully that makes some sense. , Tables ----- activity members pics quotes texts videos (i only included the necessary ones) Activity Table ----- id memberID postID type (int 1-4: 1 = pics, 2 = videos, 3 = texts, 4 = quotes) activity (int 1-4: 1 = upload, 2 = comment, 3 = like, 4 = dislike) date (* If we don't need this able in the end product, i.e. we can find a way to query without it, then i'll be fine with it being gone.) the other tables have the necessary information, e.g. id, memberID (for the pics, texts, quotes and videos), etc.
VIEWS ON THIS POST

243

Posted on:

Friday 12th October 2012
View Replies!

AES_DECRYPT cant seem to show stored values

Ok I have sucessfuly created a page that insterest data using the AES_ENCRYPT. Now I am working on another page that will show the data. I dont through any erros but I dont see the information either. I am using Mysql 5.0 and I use dreamweaver cs4. Here is the code a am using. [code] $query_Recordset1 = "SELECT AES_DECRYPT(password, 'text_key') AS decrypt FROM content"; $Recordset1 = mysql_query($query_Recordset1, $EevokePages) or die(mysql_error()); $row_Recordset1 = mysql_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysql_num_rows($Recordset1); > Untitled Document [code] Can anyone help me out here I am stuck
VIEWS ON THIS POST

227

Posted on:

Friday 12th October 2012
View Replies!

Sum(qty), where & groupby cant get it to work?

PHP Code: SELECTproductid,sum(qty) fromproductsizes groupby`productid` orderby2asc the above query works but what i want it to do is add where clause that says "where sum(qty) < 4 or sum(qty) = 0" but that gives me the error of invalid group function ----edit: got it working by using HAVING SELECT product.productid, name, sum( qty ) AS sum FROM productsizes, product WHERE productsizes.productid = product.productid GROUP BY `productid` HAVING sum( qty )
VIEWS ON THIS POST

243

Posted on:

Friday 12th October 2012
View Replies!

cant create table ..... (erno: 150)

Well morning everybody, so actually i have to do some work for my coming classtest, everything is working fine ... just the last part of my sql script. Code: CREATE DATABASE IF NOT EXISTS db_schueler; USE db_schueler; CREATE TABLE IF NOT EXISTS tbl_schueler ( id INT(4) NOT NULL AUTO_INCREMENT, name CHAR(12) NOT NULL, vorname CHAR(12) NOT NULL, adresse char(50) NOT NULL, stadt CHAR(20) NOT NULL, versetzung12 BIT NOT NULL, -- 1/0 bzw. Ja/Nein PRIMARY KEY (id) ); CREATE TABLE IF NOT EXISTS tbl_fach ( fach_id INT(4) NOT NULL AUTO_INCREMENT, tbl_schueler_id INT(4), deutsch CHAR(12) NOT NULL, mathe CHAR(12) NOT NULL, englisch CHAR(12) NOT NULL, datenbank CHAR(12) NOT NULL, programmiertechnik CHAR(12) NOT NULL, betriebnetzwerke CHAR(12) NOT NULL, PRIMARY KEY (fach_id), FOREIGN KEY (tbl_schueler_id) REFERENCES tbl_schueler(id) ); CREATE TABLE IF NOT EXISTS tbl_zensuren ( zensuren_id INT(4) NOT NULL AUTO_INCREMENT, schueler_versetzung12 BIT NOT NULL, -- 1/0 bzw. Ja/Nein prfung BIT NOT NULL, -- 1/0 bzw. Ja/Nein PRIMARY KEY(zensuren_id), FOREIGN KEY(schueler_versetzung12) REFERENCES tbl_schueler(versetzung12) ); I am getting this Error Cant Create table 'db.schueler.tbl_zensuren' (erno: 150) I Found out that this error stays in connection with the foreign key, but i have no idea. I Hope s.o can help me to fix this. Grettings
VIEWS ON THIS POST

230

Posted on:

Sunday 21st October 2012
View Replies!

can't figure out what's wrong with this mysql query

INSERT INTO state (key,value) VALUES ('non-US','Non-U.S.') I get: Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key,value) VALUES ('non-US','Non-U.S.')' at line 1 I can't see what's wrong... Can you The table: CREATE TABLE `state` ( `key` varchar(10) NOT NULL, `value` varchar(200) NOT NULL, PRIMARY KEY (`key`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
VIEWS ON THIS POST

135

Posted on:

Sunday 21st October 2012
View Replies!

Cannot figure out how to fix query. Repeats results!

I have a user login system that allows for files to be uploaded into specific folders. The problem I am experiencing is that when you go to view the files that have been uploaded, if a file was categorized into two folders, two separate entries for the same file will appear. files +------+---------+-------+ | file_id | name __| author | +------+---------+-------+ | 28 __| logo.png | 1 ____| +------+---------+-------+ files_folders +------+----------+ | file_id | folder_id | +------+----------+ | 28 ___| 3 ______| | 28 ___| 1 ______| +------+----------+ folders +---------+------------+-----------------+ | folder_id | folder_name | parent_folder_id | +---------+------------+-----------------+ | 1 ______| ABC ______| 0 _____________| | 3 ______| XYZ ______| 0 _____________| +---------+------------+-----------------+ I need to select all the files and display the folder it's in, but I cannot have duplicate entries if the file is categorized into two or more folders. My query so far is... SELECT * FROM files LEFT JOIN files_folders ON files.file_id = files_folders.file_id LEFT JOIN users on files.author = 1 LEFT JOIN folders on files_folders.folder_id = folders.folder_id ORDER BY files.file_id DESC I tried using GROUP BY but that didn't work. I also tried to group_concat the folders into one column, but I cannot figure out a query to get that to work. I would appreciate any and all help. This is giving me a headache!
VIEWS ON THIS POST

150

Posted on:

Wednesday 24th October 2012
View Replies!

Page 2 - Can't figure out a great way to do this

Quote: Originally Posted by AvsH So compile all four upload types into that single table and just leave the other fields blank, or would that be considered bad ERD design techniques it's an option, but i prefer the supertype/subtype design again... tables members uploads (new table; supertype) pics (modified; subtype) quotes (modified; subtype) texts (modified; subtype) videos (modified; subtype) activity (modified)
VIEWS ON THIS POST

303

Posted on:

Wednesday 24th October 2012
View Replies!

Cannot figure out statement with multiple nested joins

Hi folks, I am trying to make a nice tidy SQL statement using joins which does the following: Code: SELECT functionNumber FROM SystemFunction WHERE functionId in (SELECT functionId FROM SystemRoleFunction WHERE roleId in (SELECT roleId FROM SystemUserRole WHERE userId = (SELECT userId from SystemUser where userName = 'cmiuser'))) I've actually tried a bunch of different statements with joins, I tried taking out the final select statement for simplicity and doing the following "Statement Attempt One" Code: SELECT sf.functionNumber FROM (SystemRoleFunction srf JOIN SystemUserRole sur USING (roleId)) INNER JOIN SystemFunction sf USING (functionId) WHERE sur.userId = 109 "Statement Attempt Two" Code: SELECT sf.functionNumber FROM SystemFunction sf JOIN (SystemRoleFunction srf JOIN SystemUserRole sur ON sur.roleId = srf.roleId) AS mj ON mj.functionId = sf.functionId WHERE sur.userId = 109 but they both died with a SQL syntax error - and I'm not even making the the full statement in those examples! Yeek. My MySQL version is 4.1. Can anyone point me in the right direction
VIEWS ON THIS POST

120

Posted on:

Wednesday 24th October 2012
View Replies!

something about the "INSERT" query

what happened when you tested it , i don't believe there is a partial effect -- the query works or it doesn't here, i'll test it for ya first, let's set it up: Suppose that the "fielda" field is the primary key of the table, and there exists a row in the table that takes the value "value3a" in the "fielda" field. Code: CREATE TABLE tablename ( fielda VARCHAR(9) NOT NULL PRIMARY KEY , fieldb VARCHAR(37) ); INSERT INTO tablename(fielda,fieldb) VALUES ('value3a', 'already exists') ; SELECT * FROM tablename ; fielda fieldb value3a already exists now let's do your test -- Code: INSERT INTO tablename(fielda,fieldb) VALUES ('value1a', 'value1b') ,('value2a', 'value2b') ,('value3a', 'value3b') ; SQL Error (1062): Duplicate entry 'value3a' for key 1 now let's see if there are any partial results Code: SELECT * FROM tablename ; fielda fieldb value3a already exists nope
VIEWS ON THIS POST

79

Posted on:

Thursday 25th October 2012
View Replies!

Question about a query

How can I adjust the query below to return results if stories.volunteer_id is null in a reccord Code MySQL: SELECT stories.title, stories.id as 'id', stories.content as 'description', organization_name, partner_id, volunteer_id, concat(first_name, ' ', last_name ) as 'name', partners.city as 'city',partners.country as 'country' FROM stories, partners, volunteers WHERE stories.volunteer_id=volunteers.id AND stories.partner_id=partners.id ORDER BY id DESC
VIEWS ON THIS POST

82

Posted on:

Thursday 25th October 2012
View Replies!

specific output from MYSQL

hi, there are some record stored in a table and i want to retrieve some record , can i add serials numbers in front of every record i.e there is a table index name designation ============================== 1 tom clerk . . 22 jeck clerk . . 100 Harry clerk . . 1001 Rohit clerk a table like this... ------------------------------------------------------------------ i want out put like this 1. tom clerk 2. jeck clerk 3. harry clerk 4. rohit clerk means i want a serial no. with every record from database..... as 1,2,3,4 are the serial no. for every record , as its not stored in database... but i want to add it in out put... and iwant to use this serial no further in my programe... , ya its ok... but major prob is that, i want to show that records on a web pager, the records may exceed more than 1 page... so its hard to maintian a counter on diff pages... and i dont want to pas that counter argument in addressbar....
VIEWS ON THIS POST

93

Posted on:

Thursday 25th October 2012
View Replies!

Question about fulltext searching and relevancy

, I have the following query: Code: SELECT `song_name` , `song_permalink` , MATCH ( `song_name` ) AGAINST ( 'thunderstruck' IN BOOLEAN MODE ) AS relevancy FROM `songs` WHERE MATCH ( `song_name` ) AGAINST ( 'thunderstruck' IN BOOLEAN MODE ) ORDER BY `relevancy` DESC LIMIT 0 , 30 It works fine, except the relevancy is 1 for all the results. Why
VIEWS ON THIS POST

161

Posted on:

Thursday 25th October 2012
View Replies!

Help with query. Join with Order By different than without Order By

I have a query that produces an item from the forum ...links to the messages table twice...one for the ID_FIRST_MSG and the second for the ID_LAST_MSG and then adds two tables of member based on whomever wrote the messages. The first message is for the one that posts the topic and the second is for the person that last replied to it. See query here: Code: SELECT mess1.ID_MEMBER AS post_member_id, mess2.ID_MEMBER AS reply_member_id, mem1.memberName AS post_member, mem2.memberName AS reply_member FROM smf_topics LEFT JOIN smf_messages AS mess1 ON smf_topics.ID_FIRST_MSG = mess1.ID_MSG LEFT JOIN smf_members AS mem1 ON mem1.ID_MEMBER = mess1.ID_MEMBER LEFT JOIN smf_messages AS mess2 ON smf_topics.ID_LAST_MSG = mess2.ID_MSG LEFT JOIN smf_members AS mem2 ON mem2.ID_MEMBER = mess2.ID_MEMBER LIMIT 1 It produces: 4, 3, user2, user1 However, when I add an ORDER BY on it like this to do get the latest entry: Code: ORDER BY mess1.posterTime DESC It produces: 3, 3, user1, user1 What gives I've put STRAIGHT_SORT in the select as well and that didn't do anything different. Not sure why it's doing this. Any ideas , Using Inner joins will make it so I get 3, 3, user1, user1 as well (just like when using the order by). LIMIT 1 is only for getting the first item. I could have used limit 5 above because that's what I'm really using but for simplicity sake I only used the one.
VIEWS ON THIS POST

143

Posted on:

Thursday 25th October 2012
View Replies!

Question about IN()

I know this has been asked countless times before, and I'm sorry - but searches for IN and related queries are difficult because it's such a common English word Am I right in thinking that if I have a table with the id field set as a primary key, MySQL will ignore this index when using IN() For example, SELECT ... WHERE id IN(1,2,3,4,100) If this is the case, is the only other option to construct my WHERE clause like this: WHERE id = 1 OR id = 2 OR id = 3 ... Or am I just making stuff up
VIEWS ON THIS POST

87

Posted on:

Thursday 25th October 2012
View Replies!