Group_concat Function
I am attempting to pull data from multiple tables into a resultset that
I can use as an Excel spreadsheet for reporting. Code:
View Complete Forum Thread with Replies
Related Forum Messages:
GROUP_CONCAT Bug
I am using the GROUP_CONCAT() as follows: SELECT (SELECT CONCAT(ProjectName) FROM Project WHERE FIND_IN_SET(ProjectID, RelatedProjectID))AS RelatedProject We have a project tracker system and we can have one project be related to several different projects. the issue is when there is only 1 related project associated with another project, this query does not work. Is there some sort of bug with GROUP_CONCAT?
View Replies !
Group_concat() And Max
I have three tables which looks like this: Code: Table: bp_keyinfo key_id | key_title 1 | Title1 2 | Title2 Table: bp_keyword_theme k_id | k_keyid | k_nameid 1 | 1 | 1 2 | 1 | 3 3 | 1 | 4 4 | 2 | 2 5 | 2 | 5 6 | 2 | 6 (Foreign key: k_keyid. References: key_id) ........
View Replies !
Group_concat
mysql 4.1 I'm trying to use the group_concat function together with IN and I get a message "Invalid use of group function" How else can I accomplish this? "AND user.class_id IN (0, group_concat(classes.class_id))"
View Replies !
Something Like GROUP_CONCAT()
basically, i want my query to do something like this would do.Code: SELECT listings.*, GROUP_CONCAT(photos.pic SEPARATOR ','), listings.office_id FROM listings, offices JOIN photos ON photos.list_id = listings.ln WHERE listings.office_id = offices.office_id AND ln = '$ln' GROUP BY photos.pic but that doesn't work work in 4.0.x, does anyone know how i can get the same affect in mysql 4.0.x?
View Replies !
X IN GROUP_CONCAT ?
I have a query that, a bit simplified, looks like this: SELECT positions FROM (SELECT tbl.id, (GROUP_CONCAT(tbl.foo) AS positions FROM tablefoo AS tbl LEFT JOIN tablebar AS bar ON bar.lostteethbaseid = tbl.id GROUP BY tbl.id ) AS test WHERE NOT (15 IN (positions)) AND NOT (16 IN (positions)) See what I want to achive is to retrive all positions from a table using GROUP_CONCAT and then check if certain values are present in the list of positions. Positions could look something like 15,27,30. However 15 IN (positions) does NOT work like 15 IN (15,27,30) but rather like 15 IN (ཋ,27,30') I got the query to work using LIKE: positons LIKE '%15%' But I would really like to use IN instead since that would be much quicker on an indexed field. Worth noting is that casting the field "positions" from BLOB to CHAR does not help.
View Replies !
GROUP_CONCAT LIKE 2 NOT LIKE 20 Etc?
I have used group_concat to consolidate all the 'genreids' as follows in a field... 2, 3, 4 How would i perform a search of this field, so that if i wanted to find genreid 2? But not 20, 21, etc? At current ive been using: ....AND genreids LIKE '%2%' However the above obviously matches too much :/
View Replies !
Complement Of Group_concat
is there a complement function of the "group_concat" function? for example: id,items 1,'2,45,5' 2,'5,6' => convert into that format id,items 1,'2' 1,'45' 1,'5' 2,'5' 2,'6' i need to use mysql 4.1.22 (no stored procedures...)
View Replies !
Group_concat Truncated
I'm using a group_concat in a View, to set a varchar field in another table, for full_text search purposes. However, I've noticed that it's being truncated at 341 characters in the View! On re-reading the Manual, it states that "The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet." My variables are: group_concat_max_length=1024 max_allowed_packet=16777216 ....
View Replies !
Alternative To GROUP_CONCAT
I have a db of events with categories and locations. A form w/ checkboxes inserts into 2 tables the following: Table 1: event (id, title, date, description, contact) Table 2: event_search (only foreign keys) (event_id,location_id,type_id) 2 other tables are listing the locations and the types Table 3: location (International, national, all 50 us states) (id, name) Table 4: event_type (promotional, free, concert...) (id, name) Some events have multiple types and locations, so this is the only way I've figured out to have a normalized db. How can I list the events, displaying their locations and type, without using GROUP_CONCAT (only works w/ mySQL 4.1 :( )
View Replies !
Count The Result Of Group_concat?
I learned group_concat from this forum, very useful command, but I am wondering if I can * COUNT the result of group_concat * for example, the following example, I know this is a wrong clause, but I hope I can COUNT and then use the result to do some HAVING works. So is it possible to count the result of group_concat? SELECT class, sex, COUNT(group_concat(name)) as num from school group by class, sex HAVING num > 20
View Replies !
4.0.x Equivalent/alternative For Group_Concat?
The following query works great on my test machine with mySQL 4.1.x. However, because cPanel is taking their sweet time working in mySQL 4.1, I can't use this on my website. Is there 'any' way to accomplish this same type of return in mySQL 4.0.22? Code:
View Replies !
Group_concat And String Functions
I am running: MySQL Distrib 5.0.15, for pc-linux-gnu on i686 I think I have run into a limitation of the GROUP_CONCAT function but I may really be doing things incorrectly. I hope you can point me in the right direction. We have a table of installed software that includes fields for: Product, Version, OperatingSystem and MachineName. Any software product can live on several different machines and might have different versions installed on each. I was hoping to produce a list of products, versions and an aggregate of the os/machine names that have this product installed. I tried: Code:
View Replies !
Group_concat And Special Characters
I am having trouble with the group_concat function. Table A: id[INT], name[VARCHAR(255)] 0, Groot Brittannië Table B: aid[INT], type[INT], value[VARCHAR(255)] 0, 1, Groot Brittannië When i query: "SELECT name, GROUP_CONCAT(type, '=', value) FROM A JOIN B ON (B.aid = A.id) GROUP BY A.id" The special characters in the GROUP_CONCAT result column now fail, posting wrong special characters (in this case the ë turns into something weird). As far as ia checked it out, this error appears only when the GROUP_CONCAT function is used to concat the different columns together. On top of that, when B.type is of type VARCHAR, the error does not appear. It seems like GROUP_CONCAT takes the Integer type and cannot figure out which encoding to give it, so when the varchar "Groot Brittannië" comes around the output is in the wrong encoding and fails.
View Replies !
GROUP_CONCAT In Subquery - Error
HTML SELECT GROUP_CONCAT(F.name) FROM TABLEB F, TABLEC C WHERE F._id = C._id AND C.fieldC IN (1, 2, 3) I want to incorporate it into another query: HTML SELECT A.fieldA, SUM(A.fieldB) AS total, GROUP_CONCAT(A.fieldC) AS listing, (SELECT GROUP_CONCAT(F.name) FROM TABLEB F, TABLEC C WHERE F.i_id = C.i_id AND C.fieldC IN (listing) ) AS items FROM TABLEA A GROUP BY A.fieldA ORDER BY total But hat query throws the error: Quote: 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 '(SELECT GROUP_CONCAT(F.name) FROM TABLEB F, TABLEC C WHERE F.i_id at line 2
View Replies !
LIMIT 5 Only Returns 2 When Using GROUP_CONCAT
I'm trying to limit my results in the following query. When I make this query without the GROUP_CONCAT lines, I get the number of results I specified in LIMIT, when I put those lines back in, I get less than the number of results I specify with LIMIT....
View Replies !
GROUP_CONCAT Alternative For MySQL < 4.1 ?
GROUP_CONCAT is a great function...But have MySQL version < 4.1 and my hosting provider can't upgrade MySQL. Is there a way to get this result with a different SELECT query? I have a list of recipes in one table, with a list of ingredients in another. Since there are a different amount of ingredients per recipe, the 2nd table has multiple ingredients per recipe.....
View Replies !
GROUP_CONCAT Crossjoin Problems
I've been searching the forums and found this thread which seems very similar to my problem, but I'm having trouble adapting it due to the complexity of the example. Here's my demo table and problem query: CREATE TABLE items (id INT, name CHAR(5)); INSERT INTO items VALUES (1,'test'); CREATE TABLE gallery (img_id INT, item_id INT ); INSERT INTO gallery VALUES (1,1); INSERT INTO gallery VALUES (2,1); INSERT INTO gallery VALUES (3,1); CREATE TABLE gallery2 (img_id INT, item_id INT ); INSERT INTO gallery2 VALUES (10,1); INSERT INTO gallery2 VALUES (20,1); INSERT INTO gallery2 VALUES (30,1); SELECT items.*, GROUP_CONCAT(gallery2.img_id) AS gallery2, GROUP_CONCAT(gallery.img_id) AS gallery FROM items LEFT JOIN gallery2 ON gallery2.item_id=items.id LEFT JOIN gallery ON gallery.item_id=items.id WHERE items.id = 1 GROUP BY items.id However this returns : id name gallery2 gallery 1 test 10,10,10,20,20,20,30,30,30 1,2,3,1,2,3,1,2,3 Could anyone help me with how to prevent the crossjoining?
View Replies !
GROUP_CONCAT Not Ignoring Null Values
having an issue where GROUP_CONCAT is including null values in the result set. I have made sure that both columns in question are NULL by default (they originally were not) - one is VARCHAR and one is TEXT. Version is 5.0.67. Does anybody have any suggestions? Is there another way to cure this besides embedding an IF clause in the GROUP_CONCAT statement?
View Replies !
Select Group_concat Doesn't Return Numbers?!?
Say that I have a table with a with of integers and a row with text. For starters, I just want to have the integers returned in one string: select group_concat(MyIntegers) from MyTable; This gives me a lot of questionmarks: ????????? However, if I do this, I get the real result: select lower(group_concat(MyIntegers)) from MyTable; This gives me: 11,15,20,etc. If I select the string row, no extra work is needed. select group_concat(MyStrings) from MyTable; I get: "blabla,hey,there" etc. Why can't I get the numbers "as is" in the first query? I'm using the latest 5.0.19 Win32 MySQL server, running the latest ODBC driver etc. on a Windows server 2003.
View Replies !
GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version. The query is this:
View Replies !
Make GROUP_CONCAT Not Group Null Values
I have a complex SELECT statement that uses GROUP_CONCAT to group like values -- which has been drastically over simplified below. From what I can surmise, GROUP_CONCAT is treating NULL as a value -- which I don't want. Is there a way to make it ignore null values to produce the 'desired output' below? TABLE: test +------------+------------+ | col_1 | col_2 | +------------+------------+ | NULL | a | +------------+------------+ | NULL | b | +------------+------------+ | 1 | c | +------------+------------+ | 1 | d | +------------+------------+ Current Output +------------+------------+ | col_1 | col_2 | +------------+------------+ | NULL | a|b | +------------+------------+ | 1 | c|d | +------------+------------+ Desired Output +------------+------------+ | col_1 | col_2 | +------------+------------+ | NULL | a | +------------+------------+ | NULL | b | +------------+------------+ | 1 | c|d | +------------+------------+
View Replies !
Select To Return A List Columm Without GROUP_CONCAT
I allready try something like this... select name,(select ID from table where id = X.id) from table X group by name -> return a columm with all "grouped" IDs john, "12,12,1,2323,54" marta, "1222,11,2" josh, "69,87"
View Replies !
IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere! The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over £300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....
View Replies !
GROUP_CONCAT, Muti-tables And Non-unique Column Names
three tables User: id | name -----+----- 1 | Joe 2 | Sarah 3 | Ruth 4 | Abram 5 | Anna Event: id | Start.............. | End................ | user_id ---------------------------------------------------------- 2 | 2000-03-06 09:12:33 | 2000-03-06 10:00:01 | 3 3 | 2001-03-06 09:12:33 | 2001-03-06 10:00:01 | 4 4 | 2002-03-06 09:12:33 | 2002-03-06 10:00:01 | 3 5 | 2003-03-06 09:12:33 | 2003-03-06 10:00:01 | 3 6 | 2004-03-06 09:12:33 | 2004-03-06 10:00:01 | 1 7 | 2007-03-06 09:12:33 | 2007-03-06 10:00:01 | 1 Comments: id | comment_date....... | user_id | comment --------------------------------------------------------------------- 1 | 2002-03-06 09:12:51 | 3 | I signed in 2 | 2002-03-06 09:15:06 | 1 | Ruth checked out a widget 3 | 2002-03-06 09:35:16 | 3 | the widget broke 4 | 2004-03-06 09:12:33 | 4 | Marsha called I can produce a query to list each Event record in which the Event.user_id is replaced by User.name. That is simple. SELECT Event.id, Event.start, Event.end, User.name FROM Event, User WHERE User.id = Event.user_id What I want now is the query from above but which does a GROUP_CONCAT of all Comments records that fall within the Event.start / Event.end range. I was leaning towards SELECT Event.id, Event.start, Event.end, User.name GROUP_CONCAT( Comments.comment_date, ' / ', User.name, ' : ", Comments.comment SEPARATOR '<br>' ) FROM Event, User WHERE User.id = Event.user_id ???? AND Comments.comment_date BETWEEN Event.start AND Event.end GROUP BY ???? But became confused when I started trying to associate the two different instances of "User.name".
View Replies !
Max Function
I have a table with name, priority and parent as three columns...I want to get the max(priority) grouped by the parent... I am trying to get name, priority, parent, max(parent) as the four columns in the result...Could some one help me with this select name, priority, parent from table1 select max(priority) from table1 group by parent are the two select statements that I wish to combine...is it possible..?
View Replies !
SQL IN() Function
so i have a query such as SELECT first_name FROM users WHERE user_ID IN (5,4,8,19,8,4) and i want the first_name to be retrieved for EACH of the cases (eg. name is repeated twice for 4 and 8)
View Replies !
Last Function
How do you get the last row in a grouped query. I have a table with running balances. At the end of the day, I want to find out the last balance. select * from accounts group by date(dateCol) This always gives me the first row of the grouped column dateCol.
View Replies !
Avg Function
Why doesn't this work: SELECT avg(value) as avg_value from playerstats where avg_value = '100' and stattype = 'r' group by avg_value I'm trying to select the avg_value of 'r' where the avg_value = '100'.
View Replies !
Using The AVG Function
I am trying to use this query: SELECT average(price) FROM `data` WHERE id IN ( '3', '3', '6' ) to average the price of items users select. The problem is that when a user selects a value more than once it only includes it in the average once. How can I make it average all the values?
View Replies !
Now() Function
I am using now function to get current date when info is added to my table. I am using a hosting provider that is 6 hrs ahead in time zone so i always have to subtract 6hrs to get the correct time. Is there anyway I can format the time to be inserted to my time zone?
View Replies !
SOUNDEX Function
I'm using SOUNDEX mysql function to find similar sounding names from a table with 2 million distinct names. Unfortunately there is a single soundex code for every 200,000 names! Meaning there are only 200,000 distinct codes for 2 million entries. Is there any other function / library / technique to work around this immense 1:10 redundancy ? for example, soundex for 'avis', 'apex' and 'apps' is A120, but I'd like to differentiate between them in my search - meaning implement a "stricter" sound comparison than the soundex function offers.
View Replies !
MBREquals Function
Has the MBREquals function been implemented in the latest 4.1.1-alpha build. I am finding problems with that command. It says that problem is there in sql syntax. If it has been implemented. Can you please help me in the syntax of the command.I am finding the same problems with the Intersection,Union,difference,symdifferenc commands But manual says that they are not yet implemented. So I guess they are not yet implemented.
View Replies !
Equivalent Function
I use the following function in Oracle SELEC T decode(status,'A','Active','L','Active','Former') FROM Table What it means is: if status = A, return "Active", if status=L, return "Active", else return "Former" in the select statement. Decode in mysql has nothing to do with this functionality and I didnt see a function while browsing the docs online that did this. Is there an equivalent function in mySQL?
View Replies !
CONCAT Function
I'm needed to insert large BLOBs into a database. With the 1MB packet limit, sending larger amounts of data would be difficult, so I had a neat idea. I would do an initial insert of an empty record and get the auto_insert ID from the response, and then loop through, appending data to the record. My table is simple. One unsigned int auto_increment field (DataID), and one long blob field (BinaryData). When I loop through the data to send, I run: UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data here') WHERE DataID = 35 The binary data I insert I escape null characters, backslashes, single and double quotes. The data seems to insert fine. The problem is that as I increase the amount of data in the field, CONCAT seems to drop all but the last 416k of the data. Thus if I loop through adding 400k blocks at a time (Which I do) I am left with at most 800k of data in the blob field. Using 4k blocks I end up with 419k of data in the field when all is said and done. Please let me know if/when this will be fixed, and if there is a work around that might be used, r a better way to insert BLOB data is known.
View Replies !
CONCAT Function (bug?)
I'm needed to insert large BLOBs into a database. With the 1MB packet limit, sending larger amounts of data would be difficult, so I had a neat idea. I would do an initial insert of an empty record and get the auto_insert ID from the response, and then loop through, appending data to the record. My table is simple. One unsigned int auto_increment field (DataID), and one long blob field (BinaryData). When I loop through the data to send, I run: UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data here') WHERE DataID = 35 The binary data I insert I escape null characters, backslashes, single and double quotes. The data seems to insert fine. The problem is that as I increase the amount of data in the field, CONCAT seems to drop all but the last 416k of the data. Thus if I loop through adding 400k blocks at a time (Which I do) I am left with at most 800k of data in the blob field. Using 4k blocks I end up with 419k of data in the field when all is said and done.
View Replies !
A Date Function
I read that MySQL needs dates to be in the form YYYY-MM-DD before you can store them in a table. Since users probably won't enter dates in that order (more likely MM-DD-YYYY or MM/DD/YYYY) is there a MySQL date function that I can apply to the date before it is inserted into the database to format the date correctly? (or does the order of the elements have to be taken care of by the application prior to the database query?)
View Replies !
Add_date Function
I am running the query: SELECT DATE_ADD('pubdate', INTERVAL 3 YEAR) as new_date, pub_autokey from pubs but it returns a null value. What am I doing wrong? In the bigger picture, I want to take a date from pubdate column, add 3 years to it, then save that return value in a different column named pulldate. I am unsure how to do that - can I nest select and insert statements? Something like: INSERT into pubs set pulldate='(SELECT DATE_ADD('pubdate', INTERVAL 3 YEAR) as new_date, pub_autokey from pubs)'
View Replies !
Count() Function
I don't know why I cannot use the count function in mysql. I used the following query at the mysql prompt, but it gave me the syntax error at Count(*) SELECT QProgram, COUNT(*) FROM ContactUs GROUP BY QProgram;
View Replies !
Function Error
I´m trying to create a function using SQL language however even the command below that I found in the MySql docs is not working, I always get the same error whe I try to create a function Any suggestions ???? CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello, ',s,'!'); Error: 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 '(s Char(20)) Returns Char(50) RETURN CONCAT('HEllo,',s,'!')' at line 1 (State:37000, Native Code: 428)
View Replies !
Date Function?
I know there is a date function : TO_DAYS but is there a function that will turn a date object into millseconds or seconds?
View Replies !
Dateadd Function
i have a question what do you mean by this:YMD(YEARS(dateadd(month,0,'2006-10-11')),month(dateadd(month,0,'2006-10-11')),lastday(dateadd(month,0,'2006-10-11')))) can somebody explains to me what do this means, is this same as dateadd(month,0,date)?
View Replies !
Preg_replace Function
I am looking for a preg_replace function in mysql....one where I can match a substring within a body of text and replace it with a new substring. I have been looking at both the replace and match functions that mysql offers, but nothing seems to fit the bill. Can someone help me construct a simple query so I can update some text within a field without having to write a php script to do so?
View Replies !
|
TRACKER
